Post reply

substring funcation.

  • January 10, 2011 at 12:57 pm

    #229928

    i'm doing a little string parsing. Does anyone else find it odd that when using the substring function the second param is the starting pos and the third param is the length of the string. Therefore, i always have to calculate the length of the string by subtracting a few charindex commands. What a pain in the butt. I just find it odd that in sql server there is no string manipulation function where u specify the string to be searched , the starting position, and the ending position.

    sorry just venting....

  • January 10, 2011 at 1:15 pm

    #1271588

    They did chose number of characters to be the third part of the substring function.

    That's just a convention.

    In the other case, there would also have been users complaining they would have to calculate the end position. :hehe:

    Did you ever test using the datalength(yourcol) function as the third parameter of your substring function ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 10, 2011 at 1:29 pm

    #1271603

    it just seems to be redundant because everything is related to the first param. so if i have all sorts of replaces in the first param then i have to duplicate this code in all susequent params. Seems unnessasarily redundant. They should have another substringesk funaction maybe called substringStartEnd(string, starting pos, ending pos). That way i wouldn't have to duplicate code. The current way is unreadable for the layman

  • January 10, 2011 at 1:40 pm

    #1271611

    actually now that i think about it. i would still have the redundant code in the to calculate the end position. I wouldn't have to do subtraction to get the length though. So there is only one extra step in the current substring. it still is an extra step.

  • January 10, 2011 at 6:12 pm

    #1271679

    BaldingLoopMan (1/10/2011)

    i'm doing a little string parsing. Does anyone else find it odd that when using the substring function the second param is the starting pos and the third param is the length of the string. Therefore, i always have to calculate the length of the string by subtracting a few charindex commands. What a pain in the butt. I just find it odd that in sql server there is no string manipulation function where u specify the string to be searched , the starting position, and the ending position.

    sorry just venting....

    Venting is good. Keeps your head from exploding. 😛

    Shifting gears, you might want to post some readily consumable data and an explanation of what you're trying to parse. You'd be surprised at the solutions people can readily pull out of their hat for things like parsing.

    --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)
    Intro to Tally Tables and Functions

  • January 11, 2011 at 1:57 pm

    #1272088

    I don’t really need help parsing the data. I was just purposing a question. Why is there no substring function that has a starting position and ending position as parameters. W the current substring functionality I need to calculate the length based on the ending position as opposed to just plugging in the ending position. By having to calculate the string length it adds an extra step and worse it is more code and when dealing w/ string parcing the more code the more impossable to read. Cant ever tell where the params start and end etc.

    here is an example...

    DECLARE @exampleString VARCHAR(100)

    SET @exampleString = ' <result>failure</result> <reason>invalid Phone1</reason> <leadId>xxxxxxxxxxxx</leadId> <url><![CDATA[https://blahblahblah]></url> '

    SELECT @exampleString OriginalExampleString,

    Charindex('<reason>', REPLACE(@exampleString, ' ', '')) startingPos,

    Charindex('<leadId>', REPLACE(@exampleString, ' ', '')) endingPos,

    Charindex('<leadId>', REPLACE(@exampleString, ' ', '')) - Charindex('<reason>', REPLACE(@exampleString, ' ', '')) lengthOfString_NoticeTheExtraUnneededStepHere,

    Substring(REPLACE(@exampleString, ' ', ''), Charindex('<reason>', REPLACE(@exampleString, ' ', '')), Charindex('<leadId>', REPLACE(@exampleString, ' ', '')) - Charindex('<reason>', REPLACE(@exampleString, ' ', '')))

  • January 11, 2011 at 3:45 pm

    #1272122

    Maybe you could use something like this:

    select substring(LEFT(@exampleString, Charindex('<leadId>', REPLACE(@exampleString, ' ', ''))), Charindex('<reason>', REPLACE(@exampleString, ' ', '')), LEN(@exampleString))

    We don't need to know the length because from the documentation:

    If the sum of start_expression and length_expression is greater than the number of characters in value_expression, the whole value expression beginning at start_expression is returned.

  • January 12, 2011 at 5:51 am

    #1272311

    Why not create your own?

    create function dbo.SUBSTRING_TO_ENDPOS ( @string varchar(8000),

    @start int,

    @endpos int )

    returns varchar(8000)

    as

    begin

    return substring ( @string, @start, @endpos - @start + 1 )

    end

    go

    declare @test-2 varchar(100)

    set @test-2 = 'Hello to SQL Server programmers'

    select dbo.SUBSTRING_TO_ENDPOS ( @test-2, 10, 19 )

  • January 12, 2011 at 4:26 pm

    #1272718

    BaldingLoopMan (1/11/2011)

    I don’t really need help parsing the data. I was just purposing a question. Why is there no substring function that has a starting position and ending position as parameters. W the current substring functionality I need to calculate the length based on the ending position as opposed to just plugging in the ending position. By having to calculate the string length it adds an extra step and worse it is more code and when dealing w/ string parcing the more code the more impossable to read. Cant ever tell where the params start and end etc.

    here is an example...

    DECLARE @exampleString VARCHAR(100)

    SET @exampleString = ' <result>failure</result> <reason>invalid Phone1</reason> <leadId>xxxxxxxxxxxx</leadId> <url><![CDATA[https://blahblahblah]></url> '

    SELECT @exampleString OriginalExampleString,

    Charindex('<reason>', REPLACE(@exampleString, ' ', '')) startingPos,

    Charindex('<leadId>', REPLACE(@exampleString, ' ', '')) endingPos,

    Charindex('<leadId>', REPLACE(@exampleString, ' ', '')) - Charindex('<reason>', REPLACE(@exampleString, ' ', '')) lengthOfString_NoticeTheExtraUnneededStepHere,

    Substring(REPLACE(@exampleString, ' ', ''), Charindex('<reason>', REPLACE(@exampleString, ' ', '')), Charindex('<leadId>', REPLACE(@exampleString, ' ', '')) - Charindex('<reason>', REPLACE(@exampleString, ' ', '')))

    I guess they figured there was no money in it. Instead, they gave us the ability to precalulate those values in either a CTE or dTable and shoot from there. I'm not saying it's right and I agree with you... it would be nice to have a SUBSTRING function based on EndPos instead of LEN.

    Here's an example of what I speak of. You could also use the iTVF that David wrote just to clean up the code.

    DECLARE @exampleString VARCHAR(100)

    ;

    SET @exampleString = ' <result>failure</result> <reason>invalid Phone1</reason> <leadId>xxxxxxxxxxxx</leadId> <url><![CDATA[https://blahblahblah]></url> '

    ;

    WITH

    ctePositions AS

    (

    SELECT OriginalExampleString = @exampleString,

    startingPos = Charindex('<reason>', @exampleString) ,

    endingPos = Charindex('</reason>', @exampleString) + LEN('</reason>')

    )

    SELECT SUBSTRING(OriginalExampleString, startingPos, EndingPos-startingPos)

    FROM ctePositions

    ;

    Of course, I'd rewrite that to remove the RBAR and do a whole table with criteria to do the selection, instead.

    --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)
    Intro to Tally Tables and Functions

  • January 12, 2011 at 4:53 pm

    #1272730

    While we are offering alternatives to the missing function, in this case why not use the xml facilities available?

    DECLARE @exampleString xml

    SET @exampleString = ' <result>failure</result> <reason>invalid Phone1</reason> <leadId>xxxxxxxxxxxx</leadId> <url><![CDATA[https://blahblahblah]]></url> '

    SELECT convert(varchar(100),@exampleString.query('//reason[1]')) OriginalExampleString

    Also, to clean things up, you can CROSS APPLY your calculations...

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • January 12, 2011 at 8:57 pm

    #1272779

    mister.magoo (1/12/2011)

    While we are offering alternatives to the missing function, in this case why not use the xml facilities available?

    DECLARE @exampleString xml

    SET @exampleString = ' <result>failure</result> <reason>invalid Phone1</reason> <leadId>xxxxxxxxxxxx</leadId> <url><![CDATA[https://blahblahblah]]></url> '

    SELECT convert(varchar(100),@exampleString.query('//reason[1]')) OriginalExampleString

    Also, to clean things up, you can CROSS APPLY your calculations...

    I'm a lightweight when it comes to XML. What are the two slashes and the [1] for?

    --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)
    Intro to Tally Tables and Functions

  • January 13, 2011 at 12:29 am

    #1272834

    Jeff Moden (1/12/2011)

    I'm a lightweight when it comes to XML. What are the two slashes and the [1] for?

    The two slashes are the XML equivalent of a % at the start of a like...they match (in this case) to all <reason> nodes regardless of position in the xml.

    The [1] indicates the first node in the selected set of nodes.

    So all together (//<reason>)[1] means find me the first <reason> node

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • January 13, 2011 at 8:15 am

    #1273076

    mister.magoo (1/13/2011)

    Jeff Moden (1/12/2011)

    I'm a lightweight when it comes to XML. What are the two slashes and the [1] for?

    The two slashes are the XML equivalent of a % at the start of a like...they match (in this case) to all <reason> nodes regardless of position in the xml.

    The [1] indicates the first node in the selected set of nodes.

    So all together (//<reason>)[1] means find me the first <reason> node

    Perfect! Thanks, Magoo!

    --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)
    Intro to Tally Tables and Functions

  • January 13, 2011 at 9:29 am

    #1273131

    Jeff Moden (1/13/2011)

    mister.magoo (1/13/2011)

    Jeff Moden (1/12/2011)

    I'm a lightweight when it comes to XML. What are the two slashes and the [1] for?

    The two slashes are the XML equivalent of a % at the start of a like...they match (in this case) to all <reason> nodes regardless of position in the xml.

    The [1] indicates the first node in the selected set of nodes.

    So all together (//<reason>)[1] means find me the first <reason> node

    Perfect! Thanks, Magoo!

    You are most welcome - makes a nice change for some dribble of knowledge to flow the other way 😉

    (I really posted this just to get to 500 points...:hehe:)

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • January 13, 2011 at 7:38 pm

    #1273390

    mister.magoo (1/13/2011)

    Jeff Moden (1/13/2011)

    mister.magoo (1/13/2011)

    Jeff Moden (1/12/2011)

    I'm a lightweight when it comes to XML. What are the two slashes and the [1] for?

    The two slashes are the XML equivalent of a % at the start of a like...they match (in this case) to all <reason> nodes regardless of position in the xml.

    The [1] indicates the first node in the selected set of nodes.

    So all together (//<reason>)[1] means find me the first <reason> node

    Perfect! Thanks, Magoo!

    You are most welcome - makes a nice change for some dribble of knowledge to flow the other way 😉

    (I really posted this just to get to 500 points...:hehe:)

    Congrats on the 500 marker. Almost all of your posts have been quite substantial so your 500 is a whole lot more than just "500". Well done!

    --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)
    Intro to Tally Tables and Functions

    • Viewing 15 posts - 1 through 14 (of 14 total)

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