Need Help with String Functions

Question

Need Help with String Functions

cdun2

SSCertifiable

Points: 7893
More actions
March 30, 2004 at 11:33 am

#107472

Hello,
I need to parse a text string two different ways. The text string data is expressed in a field called 'T1'.
The following are two examples of what the data can look like:
Tim Smith 70
(or)
Donna Smith Jones 100
In every case, the name and number are seperated by a space, and the number will contain no more than three digits.
I need an aliased field to represent the names and another to represent the number.
I have parsed out the number as follows:
Right(T1,Reverse(CharIndex(' ',Reverse(T1),1))) as SNum
I can't quite figure out how to parse out the name portion of the string. If I use CharIndex(' ',Reverse(T1),1), that turns the string around and tells me where the first space is. From there, how do I parse to get everything to the right of that space (assuming the string is still reversed), then turn the string back around to display correctly?
I'm not sure if what I am proposing is the best way to approach this. Do you have any ideas? I am still playing with this to see what I can come up with.
Thank you for your help!
CSDunn

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

If you subscriber to SQL Server Magazine they have an article with a UDF that will actually just go and remove either the numeric or non-numeric characters from a string (or column value), you can get to it at http://www.winnetmag.com/Articles/ArticleID/41845/pg/2/2.html

Shamless self promotion - read my blog http://sirsql.net

cdun2 SSCertifiable Points: 7893 More actions · Answer 2

cdun2

SSCertifiable

Points: 7893

March 30, 2004 at 12:45 pm

#501156

Thanks, I will look into this.

CSDunn

cdun2 SSCertifiable Points: 7893 More actions · Answer 3

Actually, it just occured to me to subtract the CHARINDEX from the LEN of the data:

Left(T1,Len(T1) - CharIndex(' ',Reverse(T1),1))as NameData

CSDunn

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Try this:

DECLARE @mystr VARCHAR(20)

SET @mystr = 'Bill Fleming 123'

SELECT SUBSTRING(@mystr,1,(PATINDEX('%[0-9]%', @mystr)-1)) AS [First part],

SUBSTRING(@mystr,(PATINDEX('%[0-9]%', @mystr)), LEN(@mystr)) AS [Second part]

I tested this and typed it up just before the end of my work day, so I might have gotten a parenthesis in the wrong place or some other typo. So I hope it works for you.

-SQLBill

gljjr SSChampion Points: 10080 More actions · Answer 5

Well since you have the first part... Here is the whole thing

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id('tempdb..#Testit'))

DROP TABLE #Testit

CREATE TABLE #Testit

(

T1 varchar(25)

)

INSERT INTO #Testit VALUES('Tim Smith 70')

INSERT INTO #Testit VALUES('Donna Smith Jones 100')

SELECT NUM = Right(T1,Reverse(CharIndex(' ',Reverse(T1),1)))

, [NAME] = SUBSTRING(T1, 1, LEN(T1) - Reverse(CharIndex(' ',Reverse(T1),1)))

FROM #Testit

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.