Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Split alpha and numeric number

Author  Topic 

Starting Member

2 Posts

Posted - 2005-08-31 : 09:31:07
Hi ,

I am working on a stored proc to export data from a SQL Server table to a flat file. We have a table field with the following possible value:


I need to split these strings into two output fields(number and alpha)in SELECT statement. That is:

122345684XT -> 122345684 and XT
23339034300-XT ->23339034300 and XT
423432424523242332X ->423432424523242332 and X
422222222111111111232 -> 422222222111111111232
423842389034209XYZ -> 423842389034209 and XYZ

The length of whole string, the nemeric part and alpha character part are not fixed, but the maximum length of the whole string is nvarchar(25). Also, alpha characters are always at the end of the string. That is there is no such data:

The only possible way I can think is to use Regualar Expression. But my client doesn't like to use it.

Does anyone know how to do it? Thanks a lot for your kind help.

Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 09:36:32
Similar to this


Failing to plan is Planning to fail
Go to Top of Page

Starting Member

2 Posts

Posted - 2005-08-31 : 10:25:51
Thanks a lot for the reply. Yes, the function in the post above works perfectly for me! For other developers' reference, there are different functions defined in the above post which can do the same thing. Below is the one I take. Also, just make a little change (replace 0-9 with a-z) you can get only the alpha characters out!

if object_id('dbo.fnNumbersFromStr') > 0
drop function dbo.fnNumbersFromStr

create function dbo.fnNumbersFromStr(@str varchar(8000))
returns varchar(8000)
while patindex('%[^ 0-9]%',@str)>0
Set @str = replace(replace(replace(rtrim(ltrim(replace(@str,substring(@str,patindex('%[^ 0-9]%',@str),1),''))),' ',' þ'),'þ ',''),'þ','')
return @str
Go to Top of Page

Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-31 : 14:55:03
yeah... after I wrote that for the thread, I added one to our library here at work that takes a parameter in the form of a like character comparison...

so it looks like:
Select dbo.GetCharacters('This is the 1st test string.','0-9')
returns '1'

Select dbo.GetCharacters('This is the 1st test string.','a-z')
returns 'Thisisthestteststring'

Select dbo.GetCharacters('This is the 1st test string.',' a-z')
returns 'This is the st test string'

its quite handy actually


Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Go to Top of Page

Yak Posting Veteran

86 Posts

Posted - 2005-08-31 : 16:00:42
[code]declare @t table(s varchar(25))
insert @t
select '122345684XT' union
select '23339034300-XT' union
select '423432424523242332X' union
select '422222222111111111232' union
select '423842389034209XYZ' union
select 'ABC'

left(s,patindex('%[^0-9]%',S+' ')-1 ) nbr
,right(s,len(s)-patindex('%[^0-9]%',S+' ')+1) alpha
from @t[/code]
Go to Top of Page

Starting Member

1 Post

Posted - 2013-01-22 : 09:57:46

i am afraid your query fails to split text from numbers when a value has text character before the numerics.
TEXT07785621231 would not split as
alpha: TEXT

in your solution
Go to Top of Page

- Advertisement -