Post reply

Substring Question

  • October 27, 2017 at 4:27 pm

    #324291

    Hi,
    I trying to extract the value from a field until a special character   eg: firstname.lastname  I only want firstname have the substring working well for the most part except when there is not Period in between it then return a blank (nothing)


    Select Substring(name,0, Charindex('.',name)) as name2

    How do I stop this retuning a black where special charter doesn't exsist ?

    thanks in advance.

    -GJ

  • October 27, 2017 at 7:33 pm

    #1965646

    One way is like this...


    Select Substring(name,0, ISNULL(NULLIF(Charindex('.',name),0),8000)) as name2

    If the CHARINDEX results in a "0", the NULLIF converts it to a NULL.  Then, the ISNULL converts that NULL to "8000".  You could use LEN(name) instead of the 8000 but the 8000 won't return any trailing blanks unless they actually exist.

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

  • October 27, 2017 at 7:40 pm

    #1965647

    For those that may be getting ready to pounce on using a starting position of "0" instead of "1", it's a trick to make it so that you don't have to subtract 1 from the result of the CHARINDEX to eliminate the period.  Here's a test of that and I've encapsulated the result in pipe characters just so you can see that no blanks start showing up.


    DECLARE @TestName VARCHAR(20) = 'Jeff.Moden'
     SELECT '|'+SUBSTRING(@TestName,0, ISNULL(NULLIF(CHARINDEX('.',@TestName),0),8000))+'|' AS name2
    ;

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

  • October 28, 2017 at 7:05 am

    #1965663

    Jeff Moden - Friday, October 27, 2017 7:40 PM

    For those that may be getting ready to pounce on using a starting position of "0" instead of "1", it's a trick to make it so that you don't have to subtract 1 from the result of the CHARINDEX to eliminate the period.  Here's a test of that and I've encapsulated the result in pipe characters just so you can see that no blanks start showing up.


    DECLARE @TestName VARCHAR(20) = 'Jeff.Moden'
     SELECT '|'+SUBSTRING(@TestName,0, ISNULL(NULLIF(CHARINDEX('.',@TestName),0),8000))+'|' AS name2
    ;

    😉
    SELECT '|'+ISNULL(SUBSTRING(@TestName,0, NULLIF(CHARINDEX('.',@TestName),0)),@TestName)+'|' AS name2


    😎

  • October 28, 2017 at 8:37 am

    #1965671

    Eirikur Eiriksson - Saturday, October 28, 2017 7:05 AM

    Jeff Moden - Friday, October 27, 2017 7:40 PM

    For those that may be getting ready to pounce on using a starting position of "0" instead of "1", it's a trick to make it so that you don't have to subtract 1 from the result of the CHARINDEX to eliminate the period.  Here's a test of that and I've encapsulated the result in pipe characters just so you can see that no blanks start showing up.


    DECLARE @TestName VARCHAR(20) = 'Jeff.Moden'
     SELECT '|'+SUBSTRING(@TestName,0, ISNULL(NULLIF(CHARINDEX('.',@TestName),0),8000))+'|' AS name2
    ;

    😉
    SELECT '|'+ISNULL(SUBSTRING(@TestName,0, NULLIF(CHARINDEX('.',@TestName),0)),@TestName)+'|' AS name2


    😎

    Even Better.  Thanks, Eirikur .

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

  • October 28, 2017 at 10:41 am

    #1965675

    Jeff Moden - Saturday, October 28, 2017 8:37 AM

    Eirikur Eiriksson - Saturday, October 28, 2017 7:05 AM

    Jeff Moden - Friday, October 27, 2017 7:40 PM

    For those that may be getting ready to pounce on using a starting position of "0" instead of "1", it's a trick to make it so that you don't have to subtract 1 from the result of the CHARINDEX to eliminate the period.  Here's a test of that and I've encapsulated the result in pipe characters just so you can see that no blanks start showing up.


    DECLARE @TestName VARCHAR(20) = 'Jeff.Moden'
     SELECT '|'+SUBSTRING(@TestName,0, ISNULL(NULLIF(CHARINDEX('.',@TestName),0),8000))+'|' AS name2
    ;

    😉
    SELECT '|'+ISNULL(SUBSTRING(@TestName,0, NULLIF(CHARINDEX('.',@TestName),0)),@TestName)+'|' AS name2


    😎

    Even Better.  Thanks, Eirikur .

    You're welcome mate
    😎

  • October 30, 2017 at 8:28 am

    #1965774

    Guys thank you ! 
    That eliminated returning the blanks. However the pipe characters are now appearing at the start and end of each string - how do I remove those ?

    eg:
    |Greg|
    |Jay|
    |Andy|

    Thanks again
    -GJ

  • October 30, 2017 at 8:35 am

    #1965779

    Have you compared the performance of the given solution with simply appending the searched character at the end of the string?

    Select name, Substring(name,0, Charindex('.',name +'.')) as name2
    FROM(VALUES('Luis.Cazares'),('Luis Cazares'),('Luis Cazares.'),('.Luis Cazares'))x(name)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 30, 2017 at 8:38 am

    #1965780

    gjoelson 29755 - Monday, October 30, 2017 8:28 AM

    Guys thank you ! 
    That eliminated returning the blanks. However the pipe characters are now appearing at the start and end of each string - how do I remove those ?

    eg:
    |Greg|
    |Jay|
    |Andy|

    Thanks again
    -GJ

    Look at the code and remove the code that puts them there.  The Pipe characters are there simply to demonstrate that no additional spaces were included in the final string.

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

  • October 30, 2017 at 8:52 am

    #1965788

    Luis Cazares - Monday, October 30, 2017 8:35 AM

    Have you compared the performance of the given solution with simply appending the searched character at the end of the string?

    Select name, Substring(name,0, Charindex('.',name +'.')) as name2
    FROM(VALUES('Luis.Cazares'),('Luis Cazares'),('Luis Cazares.'),('.Luis Cazares'))x(name)

    Heh... oh yes.  Quite extensively, in fact.  It probably won't matter on such small strings but I don't know where someone looking for a similar solution, but on larger strings, might use concatenation and I want to prevent them from having a huge performance problem if they do.. 

    The extensive testing was done in the following article.  It was a bit of a revelation as to how expensive concatenation can actually be.

    Concatenation was the performance killer here

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

  • October 30, 2017 at 9:06 am

    #1965790

    Jeff Moden - Monday, October 30, 2017 8:38 AM

    gjoelson 29755 - Monday, October 30, 2017 8:28 AM

    Guys thank you ! 
    That eliminated returning the blanks. However the pipe characters are now appearing at the start and end of each string - how do I remove those ?

    eg:
    |Greg|
    |Jay|
    |Andy|

    Thanks again
    -GJ

    Look at the code and remove the code that puts them there.  The Pipe characters are there simply to demonstrate that no additional spaces were included in the final string.

    Hmmm... thank you , sorry for not trying that in the first place. 

    :pinch:

  • October 30, 2017 at 9:35 am

    #1965799

    Jeff Moden - Monday, October 30, 2017 8:52 AM

    Luis Cazares - Monday, October 30, 2017 8:35 AM

    Have you compared the performance of the given solution with simply appending the searched character at the end of the string?

    Select name, Substring(name,0, Charindex('.',name +'.')) as name2
    FROM(VALUES('Luis.Cazares'),('Luis Cazares'),('Luis Cazares.'),('.Luis Cazares'))x(name)

    Heh... oh yes.  Quite extensively, in fact.  It probably won't matter on such small strings but I don't know where someone looking for a similar solution, but on larger strings, might use concatenation and I want to prevent them from having a huge performance problem if they do.. 

    The extensive testing was done in the following article.  It was a bit of a revelation as to how expensive concatenation can actually be.

    Concatenation was the performance killer here

    I should have guessed, but I wanted to be sure. It's amazing how the simple and logical solutions sometimes end up being so bad that they can no longer be called solutions.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 30, 2017 at 10:06 am

    #1965806

    Having the one with the leading '.' not return anything just didn't seem right.  So here is what I came up with:SELECT COALESCE(SUBSTRING(Name, 1, NULLIF(NULLIF(CHARINDEX('.', Name) -1, 0), -1)), Name) AS name3
    FROM(VALUES('Luis.Cazares'),('Luis Cazares'),('Luis Cazares.'),('.Luis Cazares'))x(name)

    -- to remove leading '.' if it exists
    SELECT COALESCE(SUBSTRING(Name, CASE    WHEN SUBSTRING(Name, 1, 1) = '.'
                                                THEN 2
                                            ELSE
                                                1
                                    END, COALESCE(NULLIF(NULLIF(CHARINDEX('.', Name) -1, 0), -1), LEN(NAME))), Name) AS name4
    FROM(VALUES('Luis.Cazares'),('Luis Cazares'),('Luis Cazares.'),('.Luis Cazares'))x(name)
      

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • October 30, 2017 at 12:49 pm

    #1965844

    I'm sure this could be done in one statement, but I wanted to break it down into steps, maybe it would be easier to read.  This should only return 'Luis' or blank in this example.
    SELECT COALESCE(SUBSTRING(SUBQ2.Name, 1, COALESCE(NULLIF(NULLIF(CHARINDEX('.', SUBQ2.Name) -1, 0), -1), LEN(SUBQ2.NAME))), SUBQ2.Name) AS Name4
    FROM (SELECT SUBSTRING(SUBQ.Name, CASE    WHEN SUBSTRING(SUBQ.Name, 1, 1) = '.'
                                                THEN 2
                                            ELSE
                                                1
                                    END, LEN(SUBQ.NAME)) AS Name
            FROM (SELECT REPLACE(LTRIM(RTRIM(Name)), ' ', '.') AS NAME
                    FROM(VALUES('Luis.Cazares'),('Luis Cazares'),('Luis Cazares.'),('.Luis Cazares'), (' '))x(Name)
                    ) AS SUBQ
        ) AS SUBQ2
    ;

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • October 30, 2017 at 2:02 pm

    #1965855

    Another way, using Luis' sample data

    SELECT name, rtrim(ltrim(isnull(parsename(' '+x.name+' ', 2), x.name))) as name2
    FROM (VALUES('Luis.Cazares'),('Luis Cazares'),('Luis Cazares.'),('.Luis Cazares')) x(name);

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 15 posts - 1 through 15 (of 16 total)

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