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