| Author |
 Topic  |
|
|
srkhan30
Starting Member
United Kingdom
3 Posts |
 Posted - 07/27/2012 : 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)
Singapore
16745 Posts |
|
|
srkhan30
Starting Member
United Kingdom
3 Posts |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 07/27/2012 : 05:20:08
|
you can just use it as it is. It will give you the result that you required nicely.
KH
Time is always against us
|
 |
|
|
srkhan30
Starting Member
United Kingdom
3 Posts |
Posted - 07/27/2012 : 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
Time is always against us
|
|
|
|
Rajana
Starting Member
India
2 Posts |
Posted - 07/27/2012 : 05:40:20
|
| yes |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 07/27/2012 : 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
Time is always against us
why ?
KH
Time is always against us
|
|
|
| |
Topic |
|
substing for first, middle and end text
