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

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
0
TBSupport
Asked:
TBSupport
  • 2
1 Solution
 
Surendra NathCommented:
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
 
Surendra NathCommented:
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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