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

Tristan · Accepted Answer · 2014-08-08 09:40:23Z

1

Try this ;)

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

answered Aug 8, 2014 at 9:40

Tristan

1,0247 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tadija Bagarić Over a year ago

The 3rd argument of substring is the length of substring, not the end position.

Tadija Bagarić · Accepted Answer · 2016-11-15 07:23:43Z

0

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;

answered Nov 15, 2016 at 7:23

Tadija Bagarić

2,7213 gold badges35 silver badges49 bronze badges

Comments

Zoe - Save the data dump · Accepted Answer · 2019-03-14 15:05:36Z

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, 2019 at 15:05

Zoe - Save the data dump

28.4k22 gold badges130 silver badges163 bronze badges

answered Mar 14, 2019 at 14:46

Hasan Zafari

3953 silver badges6 bronze badges

1 Comment

Aniket G Over a year ago

Whilst this code snippet is welcome, and may provide some help, it would be greatly improved if it included an explanation 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.

Collectives™ on Stack Overflow

MSSQL select substring according to this pattern

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related