retrieving column's data

siang_wu_id

somebody please help me.... i'm using vb.net 2008 and SQL Server 2008... the case is : -------------------------------------------------- if i have data in one column like : ASD654-100-CCCEEE4444-200- what query can be used to get "CCCEEE4444" ????

Blue_Boy

SELECT SUBSTRING('ASD654-100-CCCEEE4444-200-',12,10) Instead of string value give column name.

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

thatraja

Use SUBSTRING function

SELECT SUBSTRING([ColumnName],12,10) FROM [Table]

thatraja |Chennai|India|

Tips/Tricks|Brainbench certifications

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

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