SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

dzhang
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:

122345684XT
23339034300-XT
423432424523242332X
422222222111111111232
423842389034209XYZ

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:
23444444444444XY243234Z


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.



madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 08/31/2005 :  09:36:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Similar to this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=54583

Madhivanan

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

dzhang
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
go

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

Seventhnight
Flowing Fount of Yak Knowledge

USA
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

Corey

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

VIG
Yak Posting Veteran

Israel
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'

select 
    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

livemaxx
Starting Member

United Kingdom
1 Posts

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

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
num:07785621231

in your solution
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.17 seconds. Powered By: Snitz Forums 2000