retrieving column's data

[siang_wu_id]

but i'll never know the starts index and the length of string. coz the data is randomize. example : i want to get "ASD654" and "CCCEEE4444" from the data : "ASD654-100-CCCEEE4444-200-" or to get "QWEQWEQ" and "JJJLLL" and "PPPBBMMNN" from : "QWEQWEQ-2000-JJJLLL-20-PPPBBMMNN-600-" any idea? :-D

[siang_wu_id]

it will not work. coz i'll never know the starts index and the length of string. coz the data is randomize. example : i want to get "ASD654" and "CCCEEE4444" from the data : "ASD654-100-CCCEEE4444-200-" or to get "QWEQWEQ" and "JJJLLL" and "PPPBBMMNN" from : "QWEQWEQ-2000-JJJLLL-20-PPPBBMMNN-600-" any idea? :-D is there any query for looping? :-D

[Blue_Boy]

Well, you have to use - as split char, by writting your own function then you can get result as you need.

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[siang_wu_id]

how to split char "-" in SQL ? is there a function for looping in SQL ? teach me please... i'm begging... pleaseee..... ;)

[thatraja]

For that you should write UDF, depends upon the random data you should define the things & write script for that.

thatraja |Chennai|India|

---

Tips/Tricks|Brainbench certifications

Do what you want quickly because the Doomsday on 2012 :-)

[siang_wu_id]

i agree with blueboy where i have to use "-" as split char. but the problem is i don't know how to split char using SQL Query. and i think i need function for looping using SQL Query too. can u help me pleaseee? i'm begging.... :-D

[thatraja]

Try this link SQL Server 2005 - Loop through/split a delimited string[^]

thatraja |Chennai|India|

---

Tips/Tricks|Brainbench certifications

Do what you want quickly because the Doomsday on 2012 :-)

[siang_wu_id]

wow... that code was very hard to understand.. i'm still a newbie here. but thanks alot friend. it helps and i'll try... :-D

[thatraja]

siang_wu_id wrote:

that code was very hard to understand

May be, for big works probably we need to write massive code(at least bunch of code).

siang_wu_id wrote:

i'm still a newbie here. but thanks alot friend. it helps and i'll try...

It's really great.:thumbsup:

thatraja |Chennai|India|

---

Tips/Tricks|Brainbench certifications

Do what you want quickly because the Doomsday on 2012 :-)

[RyanEK]

Hi, I suggest you create a split function

CREATE FUNCTION [dbo].[Split]
(
@delimited nvarchar(max),
@delimiter nvarchar(100))
RETURNS @t TABLE
(
id int identity(1,1),
val nvarchar(max))AS
BEGIN
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

insert into @t(val)
select
r.value('.','varchar(15)') as item
from @xml.nodes('//root/r') as records(r)

RETURN
END

Then cross apply to return your desired column. eg.

declare @tmp table (value varchar(100))
insert into @tmp values ('ASD654-100-CCCEEE4444-200-')
insert into @tmp values ('QWEQWEQ-2000-JJJLLL-20-PPPBBMMNN-600-')

select val
from @tmp
cross apply dbo.split(value,'-')
where id = 3

Ryan