Spliting the string - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Spliting the string

Author

Topic

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-26 : 00:15:27

Hi experts,

Can any one help me to split the sting generically..

i/p :100,105,asasasas.indd,1001,.....

expected o/p

col1 col2 col3 col4 ....
100 105 asasasas.indd 1001 ....

Regards

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

m_z_iqbal
Starting Member

28 Posts

Posted - 2009-03-26 : 00:53:14

http://codeinet.blogspot.com/2006/08/split-string-using-recursive-sql.html

OR

http://blogs.vbcity.com/hotdog/archive/2008/06/04/9085.aspx

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-03-26 : 00:56:30

declare @s varchar(2000),@data varchar(2000)

select @s='100,105,asasasas.indd,1001'

select @data=''''+replace(@s,',',''',''')+''''

exec('select '+@data)

Referenced by Madhivanan's blog...

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/03/15/splitting-csv-to-columns.aspx
Thanks...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-26 : 02:31:32

quote:
Originally posted by m_z_iqbal

http://codeinet.blogspot.com/2006/08/split-string-using-recursive-sql.html

OR

http://blogs.vbcity.com/hotdog/archive/2008/06/04/9085.aspx

That's different from OP's expected result
Refer ashishashish's answer

Madhivanan

Failing to plan is Planning to fail

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-03-27 : 00:53:14

Madhivanan Sir,,
How to Assign Columns name in This,,,if i want to assign column name also like as above,,,
n after that add these columns,,,,
Mean how can i assign column name in this?
Thanks In Advance...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-27 : 02:44:55

Refer Point 5
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-03-27 : 03:03:01

One more thing how to Reverse a String with out using reverse keyword in Sql...
i found one way but it is not that logically correct ....
i use substring function to do this like,,

substring(data,len(data),1)+substring(data,len(data)-1,1)+.......
but i don't think that this is logically correct so please help me out in this logic...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-27 : 05:27:17

quote:
Originally posted by ashishashish

One more thing how to Reverse a String with out using reverse keyword in Sql...
i found one way but it is not that logically correct ....
i use substring function to do this like,,

substring(data,len(data),1)+substring(data,len(data)-1,1)+.......
but i don't think that this is logically correct so please help me out in this logic...

Why do you want not to use reverse function?


1
declare @string varchar(10),@string_reversed varchar(10), @i int
select @string='abc',@i=len(@string),@string_reversed=''
while @i>0
begin
	select @string_reversed=@string_reversed+substring(@string,@i,len(@string))
	select @string=substring(@string,len(@string)-@i,len(@string)), @i=@i-1
end 
select @string_reversed

2
declare @string varchar(10),@string_reversed varchar(10)
select @string='abc',@string_reversed=''
select @string_reversed=@string_reversed+substring(@string,len(@string)-number,1) from master..spt_values
where type='p' and number between 0 and len(@string)-1
select @string_reversed

Madhivanan

Failing to plan is Planning to fail

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-03-27 : 05:53:47

Yes M able to do this the mean am able to reverse data like above u say when we pass the string......
Firstly I wrote this..
select id,ltrim(rtrim(substring(data,len(data),1)+substring(data,len(data)-1,1)+substring(data,len(data)-2,1)+substring(data,len(data)-3,1)+substring(data,len(data)-4,1)+substring(data,len(data)-5,1)+substring(data,len(data)-6,1)+substring(data,len(data)-7,1)+substring(data,len(data)-8,1))) as reverse from @t order by id desc
It Gives me correct Results.....

But mean I just Like to do in table,,,
if the values stored in table then how can i do i tried lots of things to do this,,but till now i cant found any solution for this....
if my table is like this than how can i reverse string through this table....

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Ashish'
INSERT INTO @t(data) SELECT 'Gilhotra'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-27 : 07:00:33

But why dont you use reverse function?

Madhivanan

Failing to plan is Planning to fail

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-03-27 : 07:36:56

I just get to know about the approach of this,,
I just think of cursor to do this task...but it also..
not gonna do any thing,,,i also use that thing,,,

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Ashish'
INSERT INTO @t(data) SELECT 'Gilhotra'
declare @i int,@cnt int,@data varchar(50)
select @cnt= max(len(data)) from jacob3
print @cnt
set @i=2
set @data=''
while (@i<=@cnt)
begin
select stuff(data, @i, 1, substring(data, @cnt - (@i - 1), 1)) from @t
set @i=@i+1
END
go
but this dunn give me correct results,,, but when i use this,,,,
DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Ashish'
INSERT INTO @t(data) SELECT 'Gilhotra'
declare @i int,@cnt int,@data varchar(50)
select @cnt= max(len(data)) from jacob3
print @cnt
set @i=2
set @data=''
while (@i<=@cnt)
begin
select substring(data, @cnt - (@i - 1), 1) from @t
set @i=@i+1
END
go

its give me result but i want to get that in a single row....
i tried many things on that but still...Nothing happened,,,

Thanks 4 Reply... Sir

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-27 : 09:38:40

But, tell me why do you want to complicate it without using reverse function?

Madhivanan

Failing to plan is Planning to fail

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-03-27 : 09:51:40

Without Reason Sir,,,,,
just these days learning SQL so want me to be strong in this,,,,
n also want to be a DBA n MVP in future so just always fight with queries ,,,,,
as i am from small town so i don't have enough resources to learn so ,,,what i learned till noe is just from this forum n Blogs from some people so,,
Just Want to Know About d Logic Behind this.
Thanks and waiting For Your Replies....

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-03-29 : 05:34:12

Yea So thanks to all ,,,,,,,bye the answers i found a way to get that what i want,,,

Its like this,,,
DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Ashish'
INSERT INTO @t(data) SELECT 'Gilhotra'
declare @sp varchar(10)
DECLARE CUR_Idle cursor for Select data FROM @t
OPEN CUR_Idle
fetch next from CUR_Idle into @Sp
WHILE @@FETCH_STATUS = 0
Begin
declare @string_reversed varchar(10)
declare @s table(id int identity(1,1),data varchar(50))
set @string_reversed=''
select @string_reversed=@string_reversed+substring(@sp,len(@sp)-number,1) from master..spt_values
where type='p' and number between 0 and len(@sp)-1
insert into @s(data) select @string_reversed
FETCH next from CUR_Idle into @Sp
End

CLOSE CUR_Idle
DEALLOCATE CUR_Idle
select * from @s order by id desc

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources