sql retrieve information after second occurrence of a character

Question

Post reply

sql retrieve information after second occurrence of a character

Bastiaan Kerklaan

SSC Enthusiast

Points: 173
More actions
July 7, 2022 at 11:37 am

#4060065

I my database the following error messages are created:
Company (105): Error: Sales invoice (77400375): Error during creation of purchase invoice.
Company (105): Error: Sales invoice (77400375): Unknown error. ICL invoice (77400375): Company (105): Error: Item (ADMI): Not found
Company (107): Error: Sales invoice (77400373): Error during creation of purchase invoice.
Company (107): Error: Sales invoice (77400373): Unknown error. ICL invoice (77400373): Azienda (107): Errore: Articolo (ADMI): Non riscontrato
I need to retrieve only the sales invoice number: 77400375 or 77400373, because I need to retrieve additional information using a join on this number.
I am using ,LEFT(REPLACE (REPLACE(message,'Company (105): Error: Sales invoice (',''),'Company (107): Error: Sales invoice (',''),8), but this works, but additional administration can be added and also different languages can be used in the error message.
There should be a better solution for this to make it more flexible?

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

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

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 1

What do you mean with "second occurrence of a character" ?

I cannot see that in your "Left(Replace..." example.

Have you seen these questions/articles ?

I hope this gets you going ....

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Steve Collins SSCrazy Points: 2227 More actions · Answer 2

You could try the PATINDEX function and look for the first occurrence of 8 consecutive integers enclosed in parentheses. Instead of typing the criteria 8 times this uses REPLICATE. The search criteria is declared as a variable

declare @search_criteria        varchar(100)=(select concat('%(', replicate('[0-9]', 8), ')%'));

select v.in_str, substring(v.in_str, patindex(@search_criteria, v.in_str)+1, 8) p_ndx
from (values ('Company (105): Error: Sales invoice (77400375): Error during creation of purchase invoice.'),
             ('Company (105): Error: Sales invoice (77400375): Unknown error. ICL invoice (77400375)...')
             ) v(in_str);

This reply was modified 3 years, 2 months ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Bastiaan Kerklaan SSC Enthusiast Points: 173 More actions · Answer 3

Hi Johan

The second occurence is the ( character.

After the first ( is the administration number

After the second ( is the invoice number

And I need to retrieve the invoice number

DesNorton SSC-Insane Points: 24561 More actions · Answer 4

DECLARE @ErrList table (
    LogID int NOT NULL IDENTITY(1,1)
  , ErrDesc nvarchar(4000) NOT NULL
);

INSERT INTO @ErrList ( ErrDesc )
VALUES ( 'Company (105): Error: Sales invoice (77400375): Error during creation of purchase invoice.' )
     , ( 'Company (105): Error: Sales invoice (77400375): Unknown error. ICL invoice (77400375): Company (105): Error: Item (ADMI): Not found' )
     , ( 'Company (106): Error: Sales invoice (774001): Error during creation of purchase invoice.' )
     , ( 'Company (106): Error: Sales invoice (7740012888): Unknown error. ICL invoice (77400375): Company (105): Error: Item (ADMI): Not found' )
     , ( 'Company (107): Error: Sales invoice (77400373): Error during creation of purchase invoice.' )
     , ( 'Company (107): Error: Sales invoice (77400373): Unknown error. ICL invoice (77400373): Azienda (107): Errore: Articolo (ADMI): Non riscontrato' );

SELECT el.LogID, SalesInvoiceNo = substring (el.ErrDesc, oc2.Pos +1, oc3.Pos - oc2.Pos -1)
FROM @ErrList AS el
CROSS APPLY ( VALUES ( CHARINDEX('(', el.ErrDesc) ) ) AS oc1(Pos)
CROSS APPLY ( VALUES ( CHARINDEX('(', el.ErrDesc, oc1.Pos +1) ) ) AS oc2(Pos)
CROSS APPLY ( VALUES ( CHARINDEX(')', el.ErrDesc, oc2.Pos +1) ) ) AS oc3(Pos)

How to post data/code on a forum to get the best help.

ScottPletcher SSC Guru Points: 101200 More actions · Answer 5

Here's an alternative:

SELECT E.*, LEFT(ds.Item, CHARINDEX(')', ds.Item + ')') - 1) AS Invoice
FROM @ErrList E
CROSS APPLY (SELECT Item FROM dbo.DelimitedSplit8K(ErrDesc, '(') WHERE ItemNumber = 3) AS ds

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

Steve Collins SSCrazy Points: 2227 More actions · Answer 6

Since the current code uses REPLACE with strings of equal length followed by LEFT(8), it's equivalent to SUBSTRING(input_string, 38, 8)

select v.in_str, substring(v.in_str, 38, 8) inv_num
from (values ('Company (105): Error: Sales invoice (77400375): Error during creation of purchase invoice.'),
             ('Company (105): Error: Sales invoice (77400374): Unknown error. ICL invoice (77400375)...')
             ) v(in_str);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können