Extract characters from a string

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)

Extract characters from a string

Author

Topic

cindylee
Yak Posting Veteran

55 Posts

Posted - 2005-10-11 : 20:20:33

hi i have to extract a part of a name from a string field which contains values:

name -- fieldname

john f ken
bill lawry
kent worth williams
xxx yyyy zzzz kkk
janet williams
smith

i need to get the values from first ''(space)(if it exists) to the next one (if it exists) or to the end.

f
lawry
worth
yyyy
williams
smith

i have written this one but not sure whether its efficient. iam sure there must be easy way

Select
substring(substring(name,charindex('',name)+1,len(name)),0,
case when
charindex('',substring(name,charindex('',name)+1,len(name)), 1) > 0 then charindex('',substring(name,charindex('',name)+1,len(name)), 1) else len(name)end )
from <tablename>

Thanks
Cindy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-12 : 01:59:45

Where do you want to show these data?
If you use Front End Applications like VB6 or ASP you can make use of Split function

mysplit=split(yourData,' ')

Then mysplit(1) -- if ubound>0

Otherwise refer this for split function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-12 : 02:01:41

Not sure that anything else is going to be more efficient, but here's my solution:



SET NOCOUNT ON

DECLARE @Table1 table (Column1 varchar(50))

INSERT INTO @Table1 VALUES('john f ken')
INSERT INTO @Table1 VALUES('bill lawry')
INSERT INTO @Table1 VALUES('kent worth williams')
INSERT INTO @Table1 VALUES('xxx yyyy zzzz kkk')
INSERT INTO @Table1 VALUES('janet williams ')
INSERT INTO @Table1 VALUES('smith')

SELECT 
	SUBSTRING
	(
		Column1, 
		CHARINDEX(' ', Column1) + 1, 	
		CASE 
			WHEN CHARINDEX(' ', Column1, CHARINDEX(' ', Column1) + 1) = 0 THEN DATALENGTH(Column1)
			ELSE CHARINDEX(' ', Column1, CHARINDEX(' ', Column1) + 1) - CHARINDEX(' ', Column1)
		END
	)
FROM @Table1

Tara

cindylee
Yak Posting Veteran

55 Posts

Posted - 2005-10-12 : 02:20:56

thanks tara

cindy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources