SQL Split query

Question

Post reply

SQL Split query

kkknawal

Grasshopper

Points: 20
More actions
July 4, 2009 at 12:40 am

#133657

Hi All,
I need your help to solve one query.
I have one table like below format.
create table temp(record varchar(100))
insert into temp values ('I like SQL Server')
record
I like SQL Server
I want output in below format:
record
I
like
SQL
Server
I added only one row but i want to implement this in gigantic table.
Thanks for help,
-KN

Viewing 15 posts - 1 through 15 (of 28 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

2nd version of fnParseList here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

N 56°04'39.16"
E 12°55'05.25"

WayneS SSC Guru Points: 95450 More actions · Answer 2

You might want to consider using XML.

if object_id('tempdb..#temp') is not null drop table #temp

create table #temp(record varchar(100))

insert into #temp values ('I like SQL Server')

;with CTE AS

(

select RowNbr = row_number() OVER (ORDER BY record),

MyXML = convert(XML, '' + replace(record, ' ', '') + '')

from #temp

)

select [record] = x.data.value('.', 'varchar(50)')

from CTE

cross apply CTE.MyXML.nodes('/row/value') AS x(data)

Returns:

record

------------

I

like

SQL

Server

Edit: removed extra column from output.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Good effort but won't work.

1. XML is case sensitive.

2. Run the code again, after fixing the case sensitive elements, with this test data

insert into #temp values ('I like Ike & Tina')

N 56°04'39.16"
E 12°55'05.25"

WayneS SSC Guru Points: 95450 More actions · Answer 4

Peso (7/4/2009)
Good effort but won't work.
1. XML is case sensitive.
2. Run the code again, after fixing the case sensitive elements, with this test data
insert into #temp values ('I like Ike & Tina')

Peso, would you care to comment further on #1? I know the elements are case sensitive, but I'm using the same case here. What needs fixing about case-sensitive elements?

This code fixes #2. Thanks, I forget about this frequently.

if object_id('tempdb..#temp') is not null drop table #temp

create table #temp(record varchar(100))

insert into #temp values ('I like SQL Server')

insert into #temp values ('I like Ike & Tina')

;with CTE AS

(-- replace special XML characters that cause issues in SQL. Replace delimiter (spaces) with XML tags.

select RowNbr = row_number() OVER (ORDER BY record),

MyXML = convert(XML, '' + replace(replace(replace(record,'&', '&'),'<', '<'), ' ', '') + '')

from #temp

)

select [record] = x.data.value('.', 'varchar(50)')

from CTE

cross apply CTE.MyXML.nodes('/row/value') AS x(data)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

In your CTE, ROW and VALUE elements are upper case, and in your cross apply the row and value path are lower case.

This results in an empty result. And I use a case insensitive collation.

N 56°04'39.16"
E 12°55'05.25"

LutzM SSC Guru Points: 107049 More actions · Answer 6

Peso (7/5/2009)
In your CTE, ROW and VALUE elements are upper case, and in your cross apply the row and value path are lower case.
This results in an empty result. And I use a case insensitive collation.

Hi Peso,

It looks like you've been trapped by the nice way this forum does the conversion of xml statements. Please use the "Quote" button and you'll see that the original source code is different than what you see.

"tags are all lower case"

"tags are all upper case"

"tags are mixed case"

My IE will display upper case tags all together, even though the source code is different.... WYSIWYG cannot be confirmed in this case...

It's nothing that Wayne did or didn't. It's the way the xml code is rendered (at least on my IE).

Note: I was under the same impression like you before but I've been corrected by Flo....;-)

Edit: Spelling corrected.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95450 More actions · Answer 7

lmu92 (7/5/2009)
Peso (7/5/2009)
In your CTE, ROW and VALUE elements are upper case, and in your cross apply the row and value path are lower case.
This results in an empty result. And I use a case insensitive collation.
Hi Peso,
It looks like you've been trapped by the nice way this forum does the conversion of xml statements. Please use the "Quote" button and you'll see that the original source code is different than what you see.
"tags are all lower case"
"tags are all upper case"
"tags are mixed case"
My IE will display upper case tags all together, even though the source code is different.... WYSIWYG cannot be confirmed in this case...
It's nothing that Wayne did or didn't. It's the way the xml code is rendered (at least on my IE).
Note: I was under the same impression like you before but I've been corrected by Flo....;-)
Edit: Spelling corrected.

Interesting. I've recently started using FireFox, and it displays perfectly for me.

I can see how this would be confusing... and even not work properly for others.

Edit: BTW, I see your test as all lower case. Until I quote of course.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 8

I've been bitching to Steve Jones and the others that the code windows don't work correctly with IE. One voice isn't very effective though. It would be handy if people started sending Steve, Tony Davis, and maybe even Phil Factor these types of complaints. They're tired of listening to me and appear either unwilling or unable to fix the problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95450 More actions · Answer 9

Jeff Moden (7/5/2009)
I've been bitching to Steve Jones and the others that the code windows don't work correctly with IE. One voice isn't very effective though. It would be handy if people started sending Steve, Tony Davis, and maybe even Phil Factor these types of complaints. They're tired of listening to me and appear either unwilling or unable to fix the problem.

I've started a new thread for this ... http://www.sqlservercentral.com/Forums/FindPost747379.aspx

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 10

Very cool, Wayne... Guess I'll transfer all of my test results and explanations to that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 11

Jeff Moden (7/5/2009)
Very cool, Wayne... Guess I'll transfer all of my test results and explanations to that.

I just did. Definitely not all of them but most of what I could remember/find. Hope it'll help...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

kkknawal Grasshopper Points: 20 More actions · Answer 12

kkknawal

Grasshopper

Points: 20

July 5, 2009 at 7:09 pm

#1020163

Thanks for the solution.

Appreciate it.

WayneS SSC Guru Points: 95450 More actions · Answer 13

kkknawal (7/5/2009)
Thanks for the solution.
Appreciate it.

Glad we could help you.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Mario Garcia-415929 SSC-Addicted Points: 464 More actions · Answer 14

I have used a function to parse data. It can be altered some to fit your needs.

select dbo.utilfn_split_get ('test me please', ' ', 2)

create function [dbo].[UTILfn_Split_Get](

@Stringnvarchar(4000),

@Delimiternvarchar(10),

@GetPosint

)

returns nvarchar(255)

begin

declare @NextString nvarchar(4000)

declare @Pos int

declare @NextPos int

declare @CommaCheck nvarchar(1)

declare @cnt int;

declare @ValueTable table (

[Value] nvarchar(4000), [Position] int

)

--Initialize

set @NextString = ''

set @CommaCheck = right(@String,1)

set @cnt = 0

--Check for trailing Comma, if not exists, INSERT

--if (@CommaCheck @Delimiter )

set @String = @String + @Delimiter

--Get position of first Comma

set @Pos = charindex(@Delimiter,@String)

set @NextPos = 1

--Loop while there is still a comma in the String of levels

while (@pos 0) begin

set @cnt = @cnt + 1

set @NextString = substring(@String,1,@Pos - 1)

insert into @ValueTable ( [Value], [Position] ) Values (@NextString, @cnt)

set @String = substring(@String,@pos +1,len(@String))

set @NextPos = @Pos

set @pos = charindex(@Delimiter,@String)

end

declare @value nvarchar(255)

select @value = [Value]

from @ValueTable

where [Position] = @GetPos

return @value

end