CODEWITHSUNDEEP
sql-serversql-server-2012
ebelair
ebelair

Reputation: 884

Sql Server extract string inside a text

How i can find the 'azerty' string part inside a field that containing this kind of text:

DECLARE @s VARCHAR(max)

SELECT @s='Num Fourn:FST00100
Ref Fourn:azerty
PU HT:10
Remise(%):
Coef:2

*** Veuillez remplir les infos ci-dessus sans modifier la structure des lignes ***
Exemple:
Num Fourn: FST00100
Ref Fourn: ABC123
PU HT: 5.30
Remise(%): 10+5
Coef: 2.26'

Upvotes: 0

Views: 44

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

this will make you happy - I bet!

DECLARE @s VARCHAR(max)

SELECT @s='Num Fourn:FST00100
Ref Fourn:azerty
PU HT:10
Remise(%):
Coef:2

*** Veuillez remplir les infos ci-dessus sans modifier la structure des lignes ***
Exemple:
Num Fourn: FST00100
Ref Fourn: ABC123
PU HT: 5.30
Remise(%): 10+5
Coef: 2.26';

WITH Casted(AsXml) AS
(
    SELECT CAST('<x>' + REPLACE((SELECT REPLACE(REPLACE(@s,CHAR(10),''),CHAR(13),'###LineBreak###')  AS [*] FOR XML PATH('')),'###LineBreak###','</x><x>') + '</x>' AS XML)
)
SELECT ln
      ,CASE WHEN CHARINDEX(':',ln)>0 THEN LEFT(ln,CHARINDEX(':',ln)-1) ELSE ln END AS FirstPart
      ,CASE WHEN CHARINDEX(':',ln)>0 THEN RIGHT(ln,CHARINDEX(':',REVERSE(ln))-1) ELSE ln END AS SecondPart
FROM Casted
CROSS APPLY AsXml.nodes('/x') AS A(x)
CROSS APPLY(SELECT LTRIM(RTRIM(x.value('.','nvarchar(max)')))) AS B(ln)

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this (depending on your actual data it might be necessary to change the line break from CHAR(13) to CHAR(10)...

DECLARE @s VARCHAR(max)

SELECT @s='Num Fourn:FST00100
Ref Fourn:azerty
PU HT:10
Remise(%):
Coef:2

*** Veuillez remplir les infos ci-dessus sans modifier la structure des lignes ***
Exemple:
Num Fourn: FST00100
Ref Fourn: ABC123
PU HT: 5.30
Remise(%): 10+5
Coef: 2.26';

DECLARE @pos1 INT=CHARINDEX('Ref Fourn:',@s) + LEN('Ref Fourn:');
DECLARE @pos2 INT=CHARINDEX(CHAR(13),@s,@pos1);

SELECT @pos1 AS PosStart
      ,@pos2 AS PosEnd
      ,LTRIM(RTRIM(SUBSTRING(@s,@pos1,@pos2-@pos1)));

Upvotes: 1

Related Questions