MSSQL select substring according to this pattern

Question

I have to ask you for something which is connetced with MSSQL.

So, I've got one column named Command(VARCHAR) which is a part of some table named TB_Commander.

This Column include results for example like (rows):

1.Delete o:2312312, c=312321
2.Add o:342342344, c=5

BTW. The thing I'd like to do is select substring from this rows which include only 'o:2312312' and for row number 2, only 'o:342342344'.

I'm stuck over here:

    select 
    SUBSTRING(Command,PATINDEX('%1%',Command), 
    CHARINDEX(',',Command,PATINDEX('%o=%',Command))-0) as OperationID 
    from TB_Commander 
    where IdRow = 921321

Sorry for my english...

Thanks for any hand...

score 1 · Answer 1 · answered Aug 08 '14 at 09:40

1

Try this ;)

select
substring(command, 
    patindex('%o:%', command) - 2, 
    patindex('%, c%', command)) as OperationID 
from TB_Commander 
where IdRow = 921321

answered Aug 08 '14 at 09:40

Tristan

1,004
7
14

The 3rd argument of substring is the length of substring, not the end position. – Tadija Bagarić Nov 15 '16 at 07:23

score 0 · Answer 2 · answered Nov 15 '16 at 07:23

This solution returns only the data you need, taking in to account length of patterns you use to extract the needed data:

SELECT
    SUBSTRING(command, 
    PATINDEX('%o:%', command) + LEN('o:'), 
    PATINDEX('%, c%', [FiscalizationRequest]) - PATINDEX('%o:%',[FiscalizationRequest]) - LEN('o:')
    ) as OperationID 
FROM TB_Commander;

score 0 · Answer 3 · edited Mar 14 '19 at 15:05

0

declare @i int

declare @str varchar(1000)

set @str='interesting data'

declare @pattern varchar(1000)

set @pattern='eres'

SELECT @i=PATINDEX('%ter%', @str);  

select substring(@str,@i-4,len(@pattern)+4)

edited Mar 14 '19 at 15:05

Zoe

27,060
21
118
148

answered Mar 14 '19 at 14:46

Hasan Zafari

355
2
6

Whilst this code snippet is welcome, and may provide some help, it would be [greatly improved if it included an explanation](//meta.stackexchange.com/q/114762) of *how* and *why* this solves the problem. Remember that you are answering the question for readers in the future, not just the person asking now! Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Aniket G Mar 15 '19 at 00:48

MSSQL select substring according to this pattern

3 Answers3