Problem with the text data column, variable

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

  • 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

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

    🙂

  • 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

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

  • 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

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

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

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