Problem with the text data column, variable

Question

Post reply

Problem with the text data column, variable

Viewing 7 posts - 16 through 21 (of 21 total)

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

HBhagat Hall of Fame Points: 3341 More actions · Answer 1

Hi Chris,

I am having problerm as to what have you done in the code.

I am not able to understand properly step by step how and what is happeneing..

can you please help me

Harsha.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Sure.

This statement:

SELECT number

FROM Numbers

WHERE ... SUBSTRING(@String, number, 1) = @Delimiter

means "select rows from the Numbers (tally) table only where Numbers.number is equal to the character position of a delimiter". If you have a delimiter i.e. a comma say at character positions 1, 5 and 10 in your string, then rows from the Numbers table with values of 1, 5 and 10 will be output.

Does this help?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

HBhagat Hall of Fame Points: 3341 More actions · Answer 3

SELECT CASE WHEN PATINDEX('%'+@Delimiter+'%', SUBSTRING(@String, number+1, 12)) > 0

THEN SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END,

PATINDEX('%'+@Delimiter+'%', SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END, 12))-1)

ELSE SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END,

DATALENGTH(SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END, 12))) END

FROM dbo.Numbers

WHERE number <= DATALENGTH(@String)

AND (SUBSTRING(@String, number, 1) = @Delimiter

OR number = 1)

ORDER BY number

In the above select Query I am confused as to why you have used the case statement. As far I can understand that its for the loop, but where is it increasing.

1. Why have you taken 12 as the last number why not other number!

2. what does (CASE number WHEN 1 THEN 1 ELSE number+1 ) means?

3. How the sql is behaving with the data so properly.

thanks..

🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

It's much easier to understand the second code sample posted, however:

1. The figure of 12 for the max substring length is arbitrary but should be >= the max string length in the input string. The start position of the element is provided by number but you don't know the length of the element and can't risk using CHARINDEX (normally used in a split string function) to locate it because it will fail over 8000 characters. It's not necessary for the second code sample because the code generates the leading and trailing delimiter positions for each element in the string.

2. The CASE is to get around the absence of a leading delimiter in the string. The first element in the string starts at character position 1, the second and subsequent elements start at number+1, because number is the character position of the delimiter. This is handled differently in the second sample.

3. BOL lists the limited functions and operators which work with text datatype. The code I've posted for you is modified from a string-splitting function (originally posted by Jeff Moden) and accounts for those limitations.

The easiest way to understand how the code works is to disassemble it:

DECLARE @Delimiter CHAR(1), @String VARCHAR(500)

SET @Delimiter = ','

SET @String = 'B23,8765,36548,534,3514687,351684964,654684,35468461,3555645,6568789E,'

SELECT

n.number,

SUBSTRING(@String, n.number, 1),

PATINDEX('%'+@Delimiter+'%', SUBSTRING(@String, n.number+1, 12)),

SUBSTRING(@String, n.number+1,

DATALENGTH(SUBSTRING(@String, n.number+1, 12))),

SUBSTRING(@String, CASE n.number WHEN 1 THEN 1 ELSE n.number+1 END,

DATALENGTH(SUBSTRING(@String, CASE n.number WHEN 1 THEN 1 ELSE n.number+1 END, 12))),

CASE WHEN PATINDEX('%'+@Delimiter+'%', SUBSTRING(@String, n.number+1, 12)) > 0

THEN SUBSTRING(@String, CASE n.number WHEN 1 THEN 1 ELSE n.number+1 END,

PATINDEX('%'+@Delimiter+'%', SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE n.number+1 END, 12))-1)

ELSE SUBSTRING(@String, CASE n.number WHEN 1 THEN 1 ELSE n.number+1 END,

DATALENGTH(SUBSTRING(@String, CASE n.number WHEN 1 THEN 1 ELSE n.number+1 END, 12))) END

FROM dbo.Numbers n

WHERE n.number <= DATALENGTH(@String)

AND (SUBSTRING(@String, n.number, 1) = @Delimiter

OR n.number = 1)

ORDER BY n.number

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 5

I'm pretty sure you don't need PATINDEX if you prewrap the string to be split in delimiters... please see the section titled "One Final "Split" Trick with the Tally Table" in the following article...

http://www.sqlservercentral.com/articles/TSQL/62867/

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Jeff Moden (9/2/2008)
I'm pretty sure you don't need PATINDEX if you prewrap the string to be split in delimiters... please see the section titled "One Final "Split" Trick with the Tally Table" in the following article...
http://www.sqlservercentral.com/articles/TSQL/62867/

You're absolutely right, Jeff, and if you do prewrap the string then the resulting code is far cleaner. However you can't add characters to a text value,

SET @TextValue = ','+@TextValue+','

returns an error. You could save the text value to a text column in a table as the OP was originally doing then prewrap the string using text pointers (don't think text in row would be appropriate for this because of the large value size).

Incidentally, SQL2k allows text datatype sproc parameters but not text datatype variables :crazy:

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 7

Chris Morris (9/3/2008)
However you can't add characters to a text value

You're right, Chris... my bad. I lost track of the basis of this thread. Thanks for the feedback.

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