substring + chrindex

Question

Post reply

substring + chrindex

abdalah.mehdoini

Ten Centuries

Points: 1012

January 20, 2020 at 6:46 pm

#3716874

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

This topic was modified 4 years, 10 months ago by abdalah.mehdoini.

Attachments:

You must be logged in to view attached files.

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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 4 years, 10 months ago by Phil Parkin. Reason: Fix typos

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

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.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101096 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

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: 24800 More actions · Answer 6

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

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 7

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: 18354 More actions · Answer 8

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)