March 5, 2014 at 2:55 am
hi there,
Can anybody help to understand how the following query works ?
declare @dynamic_sql as varchar(2000)
select
@dynamic_sql=REPLACE ('SELECT '''+ 'hot,sweet,wow,cool' , ',' , ' '' as tags
UNION SELECT '' ' )+''' as tags'
exec(@dynamic_sql)
March 5, 2014 at 3:23 am
It a very simple replace working. to split a comma separated data into a table. 🙂
Let break it down for understanding
Replace Function Definition Replaces all occurrences of a specified string value with another string value.
More detail refer to http://technet.microsoft.com/en-us/library/ms186862.aspx
When Replace find 1st occurrence of "," it will replace it will the string_replacement which is
' '' as tags
UNION SELECT '' '
above mentioned line doing the following
1. Place a column name as 'tag'
2. Place a union Keyword
3. Place a Select Keyword.
which is converting this
SELECT 'hot,sweet,wow,cool'
into this
SELECT 'hot ' as tags
UNION SELECT ' sweet ' as tags
UNION SELECT ' wow ' as tags
UNION SELECT ' cool' as tags
in below code i have replace the exec Command with print for better understanding
declare @dynamic_sql as varchar(2000)
select
@dynamic_sql=REPLACE ('SELECT '''+ 'hot,sweet,wow,cool' , ',' , ' '' as tags
UNION SELECT '' ' )+''' as tags'
Print @dynamic_sql
hope it helps
March 5, 2014 at 3:23 am
here in a comma separated input string ',' is replaced by " ' as tags UNION SELECT ' " so that you get a output with distinct values in one column
March 5, 2014 at 6:21 am
twin.devil (3/5/2014)
It a very simple replace working. to split a comma separated data into a table. 🙂Let break it down for understanding
Replace Function Definition Replaces all occurrences of a specified string value with another string value.
More detail refer to http://technet.microsoft.com/en-us/library/ms186862.aspx
When Replace find 1st occurrence of "," it will replace it will the string_replacement which is
' '' as tags
UNION SELECT '' '
above mentioned line doing the following
1. Place a column name as 'tag'
2. Place a union Keyword
3. Place a Select Keyword.
which is converting this
SELECT 'hot,sweet,wow,cool'
into this
SELECT 'hot ' as tags
UNION SELECT ' sweet ' as tags
UNION SELECT ' wow ' as tags
UNION SELECT ' cool' as tags
in below code i have replace the exec Command with print for better understanding
declare @dynamic_sql as varchar(2000)
select
@dynamic_sql=REPLACE ('SELECT '''+ 'hot,sweet,wow,cool' , ',' , ' '' as tags
UNION SELECT '' ' )+''' as tags'
Print @dynamic_sql
hope it helps
Yep. it helps me.
But still have doubt about how it works as looping ?? how it go through all of the commas ?
March 5, 2014 at 6:29 am
what kind of doubts you have ?
March 5, 2014 at 6:30 am
twin.devil (3/5/2014)
what kind of doubts you have ?
how it works as looping ?? how it go through all of the commas ?
March 5, 2014 at 6:47 am
vignesh.ms (3/5/2014)
twin.devil (3/5/2014)
what kind of doubts you have ?how it works as looping ?? how it go through all of the commas ?
Sorry Guys just a misunderstanding ..
now i got it ...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply