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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Split alpha and numeric number
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 08/31/2005 :  09:31:07  Show Profile  Reply with Quote
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 - 08/31/2005 :  09:36:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Similar to this


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

Starting Member

2 Posts

Posted - 08/31/2005 :  10:25:51  Show Profile  Reply with Quote
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

Flowing Fount of Yak Knowledge

2878 Posts

Posted - 08/31/2005 :  14:55:03  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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 - 08/31/2005 :  16:00:42  Show Profile  Reply with Quote
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
Go to Top of Page

Starting Member

United Kingdom
1 Posts

Posted - 01/22/2013 :  09:57:46  Show Profile  Reply with Quote

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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000