July 4, 2009 at 12:40 am
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
July 4, 2009 at 2:07 am
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"
July 4, 2009 at 9:40 am
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
July 4, 2009 at 12:46 pm
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"
July 4, 2009 at 2:44 pm
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
July 5, 2009 at 1:05 am
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"
July 5, 2009 at 3:50 am
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.
July 5, 2009 at 9:35 am
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
July 5, 2009 at 10:54 am
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
Change is inevitable... Change for the better is not.
July 5, 2009 at 11:38 am
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
July 5, 2009 at 4:51 pm
Very cool, Wayne... Guess I'll transfer all of my test results and explanations to that.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2009 at 5:17 pm
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...
July 5, 2009 at 7:09 pm
Thanks for the solution.
Appreciate it.
July 5, 2009 at 8:45 pm
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
July 6, 2009 at 7:41 am
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
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply