question

David 2 1 avatar image
David 2 1 asked

How To Return Only Alphabetical Characters From A Query?

How do I extract the first alphabetical characters from a postcode column?

For example say my table contains the following data (note: it's alphanumeric and the numbers can start on the 2 or 3 digit):

POSTCODE

AB123DF D546WQ3 FG2341F DE33444 H765431

How do I query it to return:

POSTCODE

AB D FG DE H

Basically the first 2 characters need to be returned but any numbers in that returned set are truncated/trimmed from the result so only alphabetical ones show. Is this possible?

Thanks.

t-sqlsql-server-2000
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TG avatar image TG commented ·
Yes, but your description doesn't match your result. ie: What about "W" and "Q"? Not all the letter from the original are returned in your desired output?
0 Likes 0 ·
TG avatar image
TG answered

To simply take out the numeric digits you can use a nested replace

select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(            
       'AB123DF D546WQ3 FG2341F DE33444 H765431'            
       ,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')            
            
OUTPUT:            
ABDF DWQ FGF DE H            

Please see my comments about your description of what you want not matching your exptected results.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Madhivanan avatar image
Madhivanan answered

Another method

declare @data varchar(100),@newdata varchar(100)            
set @data='AB123DF D546WQ3 FG2341F DE33444 H765431'            
set @newdata=''            
select @newdata=@newdata+case when data like '%[0-9]%' then '' else data end from            
(            
select substring(@data,number,1) as data from master..spt_values            
where type='p' and number between 1 and len(@data)            
) as t            
            
select @newdata            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.