Post reply

I am struggling with selecting specific information out of a strings

  • October 23, 2018 at 10:21 am

    #2010810

    Jonathan AC Roberts - Tuesday, October 23, 2018 9:38 AM

    ScottPletcher - Tuesday, October 23, 2018 9:32 AM

    Michael L John - Tuesday, October 23, 2018 9:24 AM

    nick.hinton - Tuesday, October 23, 2018 9:21 AM

    Try using the Parsename function:
    https://docs.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-2017

    Considering that the OP is using SQL 2008, that is not available to them.

    PARSENAME has been around for a long time, as I recall it.  I think he was even available in 2005, although I don't have an instance of that around to check it.

    It says 2012 on the BOL:
    https://docs.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-2017

    PARSENAME (Transact-SQL)
    APPLIES TO: yesSQL Server (starting with 2012) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

    Those notations of Beginning with also often default to SQL Server 2008. So if you want to follow those that Microsoft lists, you can only do a select if you are on SQL Server 2008, it didn't exist on earlier versions of SQL Server:
    SELECT (Transact-SQL)

    So many of the "SQL Server (starting with 20nn)" notations in the documentation are incorrect. It's become useless to give them much credence.

    Sue

  • October 23, 2018 at 10:40 am

    #2010820

    nick.hinton - Tuesday, October 23, 2018 9:49 AM

    It works in a 2008 r2 instance, and as I say here is a post about it from 2003:
    parsename

    Normally that would be a good idea but not in this case.  The problem with PARSNAME , in this case, is that it parses from right to left and the data has an uneven number of periods in the data.  You would also have to concatenate parts 1 and 2 together with a period for the 2 part rows and condition logic to handle the 2 part rows.  It also converts the varchar components to NVARCHAR(128), which could cause serious implicit cast problems which could also cause serious non-SARGability problems with any JOINs or WHERE clause criteria.

    --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 23, 2018 at 10:45 am

    #2010821

    randyetheridge - Tuesday, October 23, 2018 9:55 AM

    ok the parse code worked perfectly
     SELECT Company = SUBSTRING(sd.AccountNumber,1,ca.ploc-1)
    ,JDEAccountnumber = SUBSTRING(sd.AccountNumber,ca.ploc+1,50)
    FROM dbo.Max$ sd
      CROSS APPLY (SELECT CHARINDEX('.',sd.AccountNumber)) ca (ploc)

    I will  do some research and reverse engineer the code so I understand how to use it in the future.  thanks to all who replied.

    To summarize, the CROSS APPLY is actually a fast per-row correlated subquery under the hood and it calculates the position of the first period for every row.  The SUBSTRING functions work just exactly the same way as the way you used them but simply have shorter references (to the CROSS APPLY result) making the code a whole lot easier to read once you understand what the CROSS APPLY does.  There are other ways to do exactly the same thing using a CTE and other methods but this seemed to be the easiest to understand.  The CROSS APPLY is being used to calculate where the first period is for each row and then that is being used in the SELECT for each column.

    --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 3 posts - 16 through 17 (of 17 total)

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