T-SQ: Extract Only Letters from an Alphanumeric String

Hello:

I have a field called UOMSCHDL that contains alphanumeric characters.  The following represents examples of data returned for this field:  2YD, LB, EA, EA2, PKG, RL, CS0.

I want to return just the alpha and not the numeric.  Is there a simple means of extracting just the letters from a string containing both letters and numbers?

Thanks!

TBSupport
LVL 1
TBSupportAsked:
Who is Participating?
 
Surendra NathTechnology LeadCommented:
I just declared variables here for convinience and to show that it works.

You can just take the complete replace statement below and apply it on a column or a variable ....

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(<your Column or Variable>,'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'0','')

Open in new window


but if you are looking for some function from microsoft, then I dont there exists one for this purpose.
0
 
Surendra NathTechnology LeadCommented:
check this out

DECLARE @T VARCHAR(1000)
DECLARE @AlphaWithCommas VARCHAR(1000)
DECLARE @AlphaWithOutCommas VARCHAR(1000)
SET @T = '2YD, LB, EA, EA2, PKG, RL, CS0'
SELECT @AlphaWithCommas = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@T,'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'0','')
select @AlphaWithCommas
--Remove Commas as well
select @AlphaWithOutCommas = REPLACE(@AlphaWithCommas,',','')
select @AlphaWithOutCommas

Open in new window

0
 
TBSupportAuthor Commented:
Thanks, Surendra!  

But, there's not a way of doing this without having to create a program declaring variables?

TBSupport
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.