Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Looping nvarchar(max) delimited content and detecting newline

Looking for an example of looping nvarchar(max) delimited content and detecting newline.  Once a new line is detected ill need to do some validation of that lines content before moving onto the next.

Thank you in advance.
Keith
0
westbergk
Asked:
westbergk
  • 2
  • 2
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
You can use this procedure to split on a char.

select item into #temp
from dbo.fn_txt_split ('1,,2,,3',',,')  --replace the '1,,2,,3' with your text, ',,' with the new line characters you're using.

-- now holds the rows to validate.
select * from #temp

note that you can do '\r\n' as the characters to split on if needed.  The splitter doesn't have to be just one character.




USE [IVCILocal]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 04/29/2014 14:24:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




Create Function [dbo].[fn_Txt_Split]( 
    @sInputList varchar(8000) -- List of delimited items 
  , @Delimiter char(1) = ',' -- delimiter that separates items 
) 
RETURNS @list table (Item varchar(8000)) 
as begin 
DECLARE @Item Varchar(8000) 
  
  

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 



GO

Open in new window

0
 
westbergkAuthor Commented:
So if i had to tinker with each line in some way, how would you imagine it playing out if the nvarchar(max) contained the following.  To keep it simple lets say i just want to get the character count of each line.

a,b,c
1,2,3
9,8,7

thanks again
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
you could do
select len(item) from #temp

if you needed to validate a certain length:
if (select count(*) from #temp where len(item) != <MYLENGTH) >)  0
  -- error
else
  -- procede

Open in new window

if you need to process line by line I would do something like:

declare @line varchar(max)

while (select count (*) from #temp) > 0
begin
   set @line = (select top 1 item from #temp)
   ... do stuff
   set rowcount 1  --ensure there's only one row getting deleted
   delete from #temp
   set rowcount 0 -- go back to affecting all rows
end

Open in new window

0
 
DultonCommented:
Here's a good option, it's not a 101 solution, as it involves SQLCLR.
String splitting is one of the places a CLR function will blow away t-sql in terms of performance. Even if you don't implement it, it's a good read.

I've used a tweaked version of Adam Machanic's SQLCLR solution for several years now:
http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

It's fast.

After you create and load the clr assembly, it's called like any other table-valued function. I've found that you can order the items coming out of the function in the order in which they were in the string

SELECT ssm.item
            ,ROW_NUMBER() OVER(ORDER BY @MyDelimiter) AS  [SplitOrder]
 FROM dbo.SplitString_Multi(@MyNvarcharmaxCol,@MyDelimiter) as ssm

By having the items numbered, with a few nested CTE's you can do set-based manipulation on your dataset, rather than RBAR, cursors. If you're just manipulating the dataset, it can probably be done as a set-based solution.
0
 
westbergkAuthor Commented:
Thank you guys for the help.  This worked like a charm.  =)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now