Split alpha and numeric number

Please start any new threads on our new site at https://forums.sqlteam.com. 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

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

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

22864 Posts

Posted - 2005-08-31 : 09:36:32

Similar to this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=54583

Madhivanan

Failing to plan is Planning to fail

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

Seventhnight
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

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

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

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

livemaxx
Starting Member

1 Post

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources