substring + chrindex

  • abdalah.mehdoini

    SSC Eights!

    Points: 888

     

    Good morning all

    i need your expertise to help me on a code

    the goal is to extract part of a chain

    create table DATACONTENT
    (
    line varchar(max)
    )

    insert into DATACONTENT values ('\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIAN_1\C842183\CARREFOUR SANTE REPRISE\160010210006\documents entrants\Courriers divers_12470290.pdf')

    insert into DATACONTENT values (
    '\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIANZ_1\CAL000010\Volvo Allianz\VAZP100027\documents entrants\001-Doc 02 mars 2018 à 1640_22459358.pdf')

    insert into DATACONTENT values (
    '\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIANZ_1\CAL000027\Volvo Allianz\VAZP100002\documents entrants\Gestion Contrat Volvo - Votre Contrat VAZP100002_16821788.MSG')

    insert into DATACONTENT values (
    '\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIANZ_1\CAL023013\Ford Assurance Automobile\FORP05090\documents entrants\Retour AR_19178619.pdf')



    I must recover for each insertion the value of the ref_contrat which corresponds to

     

    160010210006

    VAZP100027

    VAZP100002

    FORP05090

     

    Sans titre

    thanks for your help

    Attachments:
    You must be logged in to view attached files.
  • Phil Parkin

    SSC Guru

    Points: 244732

    You need to split your string on the backslash and choose the 10th item. Here's an example using the infamous DelimitedSplit8k (notice how I changed your tabledef to VARCHAR(8000) to make best use of it).

    DROP TABLE IF EXISTS #DataContent;

    CREATE TABLE #DATACONTENT
    (
    line VARCHAR(8000) NOT NULL
    );

    INSERT INTO #DATACONTENT
    (
    line
    )
    VALUES
    ('\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIAN_1\C842183\CARREFOUR SANTE REPRISE\160010210006\documents entrants\Courriers divers_12470290.pdf')
    ,('\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIANZ_1\CAL000010\Volvo Allianz\VAZP100027\documents entrants\001-Doc 02 mars 2018 à 1640_22459358.pdf')
    ,('\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIANZ_1\CAL000027\Volvo Allianz\VAZP100002\documents entrants\Gestion Contrat Volvo - Votre Contrat VAZP100002_16821788.MSG')
    ,('\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIANZ_1\CAL023013\Ford Assurance Automobile\FORP05090\documents entrants\Retour AR_19178619.pdf');

    SELECT d.line
    ,s1.Item
    FROM #DATACONTENT d
    CROSS APPLY
    (SELECT * FROM dbo.DelimitedSplit8K(d.line, '\') dsk ) s1
    WHERE s1.ItemNumber = 10;

    • This reply was modified 8 months ago by  Phil Parkin. Reason: Fix typos

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Mr. Brian Gale

    SSC-Insane

    Points: 23164

    A different approach would be to tackle this at the application level. The application will grab the entire string from the database and split it in C# (or whichever language you prefer).

    If this NEEDS to be done on the SQL Server side, Phil's solution is likely going to be the best bet.  Or if you upgrade to SQL 2016, there is a built-in string splitting function that could handle it without needing to create the DelimitedSplit8k function.

    Every case that I have had where I need to work with string splitting, splitting and managing it on the application layer has had better performance.

  • Phil Parkin

    SSC Guru

    Points: 244732

    Mr. Brian Gale wrote:

    Or if you upgrade to SQL 2016, there is a built-in string splitting function that could handle it without needing to create the DelimitedSplit8k function.

    You're referring to STRING_SPLIT(), I presume.

    What this function does not do is provide the ordinal position of the split strings.

    I just tried this unstable-looking idea

    WITH ordered AS (
    SELECT *, ro = ROW_NUMBER() OVER (PARTITION BY d.line ORDER BY (SELECT NULL))
    FROM #DATACONTENT d
    CROSS APPLY
    STRING_SPLIT(d.line,'\') ss)
    SELECT * FROM ordered WHERE ro = 10

    It seems to work in this case, but I wouldn't trust it in a production scenario.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • ScottPletcher

    SSC Guru

    Points: 98548

    STRING_SPLIT also does not guarantee the order of the strings returned (in keeping with relational theory).  That is, you can't be sure that the 10th value that comes out will be the 10th value that was in the string.  It might be, but then again, it might not.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jonathan AC Roberts

    SSCoach

    Points: 17334

    Remarks

    STRING_SPLIT inputs a string that has delimited substrings, and inputs one character to use as the delimiter or separator. STRING_SPLIT outputs a single-column table whose rows contain the substrings. The name of the output column is value.

    The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string. You can override the final sort order by using an ORDER BY clause on the SELECT statement (ORDER BY value).

    https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

  • Mr. Brian Gale

    SSC-Insane

    Points: 23164

    I was reading that document from microsoft too and I think I interpreted it differently (and incorrectly) than everyone else.  I read it as the order is not guaranteed as you can put in an ORDER BY.  Which I didn't really understand why they bothered to state that.  But then re-read it and it starts with "the output rows might be in any order".  So yeah, string_split in this case is not a valid option.

    In all of the examples I have seen and tested on my system (which is not an exhaustive list), it seems like the order for the output is always the same as the input UNLESS you specify to order it differently.  That being said, it could be the examples I tried were not good ones that cause the row order to come out differently or maybe you need a larger data set for the order to go crazy.  But since the results could come out in any order, I agree that this is likely not a good route.

    My personal preference for string splitting is still in the application layer though...

  • Tom Goltl

    SSCertifiable

    Points: 5705

    You could try something like this to make it work.

    CREATE TABLE #DATACONTENT(line VARCHAR(MAX))
    INSERT INTO #DATACONTENT VALUES ('\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIAN_1\C842183\CARREFOUR SANTE REPRISE\160010210006\documents entrants\Courriers divers_12470290.pdf')
    INSERT INTO #DATACONTENT VALUES ('\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIANZ_1\CAL000010\Volvo Allianz\VAZP100027\documents entrants\001-Doc 02 mars 2018 à 1640_22459358.pdf')
    INSERT INTO #DATACONTENT VALUES ('\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIANZ_1\CAL000027\Volvo Allianz\VAZP100002\documents entrants\Gestion Contrat Volvo - Votre Contrat VAZP100002_16821788.MSG')
    INSERT INTO #DATACONTENT VALUES ('\\A1-SRV-05\REPRISE-sollyaza\Allian_ged\NOVAXEL\ALLIANZ_1\CAL023013\Ford Assurance Automobile\FORP05090\documents entrants\Retour AR_19178619.pdf')
    SELECT REVERSE(SUBSTRING(REVERSE(line),1,CHARINDEX('\',REVERSE(line),0)-1)), * FROM #DATACONTENTWHERE AS line LIKE '%\160010210006\%'
    DROP TABLE #DATACONTENT
  • Jonathan AC Roberts

    SSCoach

    Points: 17334

    Here's another way of doing it using cross applys to work through the backslashes:

    SELECT i.Result, a.line
    FROM DATACONTENT a
    CROSS APPLY(VALUES (SUBSTRING(a.line,CHARINDEX('\',a.line,3)+1,8000))) b(line)
    CROSS APPLY(VALUES (SUBSTRING(b.line,CHARINDEX('\',b.line)+1,8000))) c(line)
    CROSS APPLY(VALUES (SUBSTRING(c.line,CHARINDEX('\',c.line)+1,8000))) d(line)
    CROSS APPLY(VALUES (SUBSTRING(d.line,CHARINDEX('\',d.line)+1,8000))) e(line)
    CROSS APPLY(VALUES (SUBSTRING(e.line,CHARINDEX('\',e.line)+1,8000))) f(line)
    CROSS APPLY(VALUES (SUBSTRING(f.line,CHARINDEX('\',f.line)+1,8000))) g(line)
    CROSS APPLY(VALUES (SUBSTRING(g.line,CHARINDEX('\',g.line)+1,8000))) h(line)
    CROSS APPLY(VALUES (LEFT(h.line,CHARINDEX('\',h.line)-1))) i(Result)

     

Viewing 9 posts - 1 through 9 (of 9 total)

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