How it works ?

Question

Post reply

How it works ?

vignesh.ms

Hall of Fame

Points: 3456
More actions
March 5, 2014 at 2:55 am

#284189

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)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

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

SQLCJ SSCommitted Points: 1791 More actions · Answer 2

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

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 3

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 ?

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

Umer Akram

SSC-Insane

Points: 22209

March 5, 2014 at 6:29 am

#1694265

what kind of doubts you have ?

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 5

twin.devil (3/5/2014)
what kind of doubts you have ?

how it works as looping ?? how it go through all of the commas ?

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 6

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 ...