• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2211
  • Last Modified:

T-SQ: Extract Only Letters from an Alphanumeric String


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?


  • 2
1 Solution
Surendra NathTechnology LeadCommented:
check this out

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

TBSupportAuthor Commented:
Thanks, Surendra!  

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

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.
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now