substing for first, middle and end text

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 2008 Forums

Transact-SQL (2008)

substing for first, middle and end text

Author

Topic

srkhan30
Starting Member

3 Posts

Posted - 2012-07-27 : 04:41:01

I have a row which has column Names -
data: 'Mike Smith;John Ward;David Law'

I need to saperate out to three rows each row should have 1 name.

--1st Query
declare @String varchar(500)
set @String = 'Mike Smith;Johnathan Ward;David Law'
select substring(@String, 0,charindex(';',@String)) AS Names
union all

--2nd Query
select substring(@String, patindex('%;%',@String)+1, patindex('%;%',reverse(@String))) AS Names
union all

--3rd Query
select right(@String,patindex('%;%',reverse(@String))-1) AS Names

The column Names should return:
Mike Smith
Johnathan Ward
David Law

The first row and third row are fine, I have having problem in second which is middle row, middle is returning some part of it not complete name,could someone please help me. Many Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-27 : 04:51:24

use fnParseList
from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

srkhan30
Starting Member

3 Posts

Posted - 2012-07-27 : 05:18:01

I want in substing functions or left and right.
fnParseList will be bit over coding.

quote:
Originally posted by khtan

use fnParseList
from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-27 : 05:20:08

you can just use it as it is. It will give you the result that you required nicely.

KH
[spoiler]Time is always against us[/spoiler]

srkhan30
Starting Member

3 Posts

Posted - 2012-07-27 : 05:29:54

give me a example of it, i cant use this function.

quote:
Originally posted by khtan

you can just use it as it is. It will give you the result that you required nicely.

KH
[spoiler]Time is always against us[/spoiler]

Rajana
Starting Member

2 Posts

Posted - 2012-07-27 : 05:40:20

yes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-27 : 06:04:02

quote:
Originally posted by srkhan30

give me a example of it, i cant use this function.

quote:
Originally posted by khtan

you can just use it as it is. It will give you the result that you required nicely.

KH
[spoiler]Time is always against us[/spoiler]

why ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources