Select part of the String and update in SQL Server

Question

I have a data as follows

+-------+----------------------------------------------+
| ID    | COMMENT                                      |
+-------+----------------------------------------------+
| 3118- | Replace Id.NO 3117-52-96 Was wrongly updated |
+-------+----------------------------------------------+
| 4857  | Replace Id.NO.4875-21-96-due to 2 mistake    |
+-------+----------------------------------------------+
| 5877  | replace .ID NO 5876.69.49 due mistake 101    |
+-------+----------------------------------------------+
| 1254  | Replace Id No. 1259-93-87 due to mistake 81  |
+-------+----------------------------------------------+

I want to get the values after the No and before some words. Something like below

+-------+----------------------------------------------+------------+
| ID    | COMMENT                                      | NEW_VALUE  |
+-------+----------------------------------------------+------------+
| 3118- | Replace Id.NO 3117-52-96 Was wrongly updated | 3117-52-96 |
+-------+----------------------------------------------+------------+
| 4857  | Replace Id.NO.4875-21-96-due to mistake      | 4875-21-96 |
+-------+----------------------------------------------+------------+
| 5877  | replace .ID NO 5876.69.49 due mistake        | 5876.69.49 |
+-------+----------------------------------------------+------------+
| 1254  | Replace Id No. 1259-93-87 due to mistake     | 1259-93-87 |
+-------+----------------------------------------------+------------+

Then I have to update the ID column with NEW_VALUE. Once I get the NEW_VALUE, it will be easy to update.

What I have tried.

SELECT ID,COMMENT,
REPLACE(REPLACE(COMMENT,'Replace Id.NO',''),'Replace Id.NO.','')FROM MYTABLE

Like above i'm using multiple(around 10) REPLACE to get my required value. I'm sure there should be some easy way.

What characters are permissable? I notice that both - and . are ok. What about / or ' ' (a single white space)? if ' ' is "ok", what would you expect to see if something like the string 'Replace ID no 123 456 789 2 orders were wrong'? Is a "No" always 3 blocks of numbers? — Thom A
– Thom A ♦, Commented Jan 16, 2020 at 12:33
@Larnu, "No" will have some separator. There will not be any empty separater. It may be 3 or max 4 blocks but with some separator. — Avinash
– Avinash, Commented Jan 16, 2020 at 12:36
So what separators could it be? What is a permissible seperator? — Thom A
– Thom A ♦, Commented Jan 16, 2020 at 12:44

Thom A · Accepted Answer · 2020-01-16 12:55:11Z

3

One suggestion:

SELECT V.ID,
       V.Comment,
       SUBSTRING(V.Comment,PI.I+3,CI.I) AS NewComment
FROM (VALUES(3118,'Replace Id.NO 3117-52-96 Was wrongly updated'),
            (4857,'Replace Id.NO.4875-21-96-due to 2 mistake'),
            (5877,'replace .ID NO 5876.69.49 due mistake 101'),
            (1254,'Replace Id No. 1259-93-87 due to mistake 81'))V(ID,Comment)
      CROSS APPLY (VALUES(PATINDEX('%No[ .]%', V.Comment)))PI(I)
      CROSS APPLY (VALUES(PATINDEX('%[^0-9.-]%',STUFF(V.Comment,1,PI.I+3,'')))) CI(I);

This uses PATINDEX to find the Position of 'No '/'No.', and then the first position of a character that isn't a number of delimiter (0-9 or a . or - character).

Note that for the string 'Replace Id.NO.4875-21-96-due to 2 mistake' the value '4875-21-96-' is returned, due to the trailing delimiter on the value.

Ideally, what you need to be doing is fixing your design here, which I assume is why you are undertaking this. As a result you'll likely need to manually "mop up" any anonalies due to the poor data.

answered Jan 16, 2020 at 12:55

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

4 Comments

Avinash Over a year ago

Thanks a lot @Larnu. This is neat. It is working for all except when the string is like Replace Id No . 1265-92-87 due to mistake 81. space after No and before .. is there any way to fix that. If not i will do that one with REPLACE not a problem.

Avinash Over a year ago

Also can you please explain me PI.I+3. Not able to get that

Thom A Over a year ago

I expected some consistency to your data, @Avinash . You could use REPLACE to fix the really bad data first, yes.

Thom A Over a year ago

+3 means add 3 to the value of PI.I.

Yogesh Sharma · Accepted Answer · 2020-01-16 13:15:32Z

1

You can use PATINDEX() :

SELECT mt.id, mt.COMMENT, SUBSTRING(mt.comment, PATINDEX('%[0-9]%', mt.comment)-1, 10)
FROM MYTABLE mt;

This will assumes comment have only one number & contains 10 length.

EDIT :

SELECT mt.id, mt.COMMENT, SUBSTRING(mtt.comments, 1, PATINDEX('%[A-Z]%', mtt.comments)-2)
FROM MYTABLE mt CROSS APPLY
     ( VALUES (SUBSTRING(mt.comment, PATINDEX('%[0-9]%', mt.comment), LEN(mt.comment)))
     ) tt(comments)

edited Jan 16, 2020 at 13:15

answered Jan 16, 2020 at 12:32

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

5 Comments

Avinash Over a year ago

Unfortunately , The length is not always 10. it can vary from 7-10 .In those cases i'm getting wrong output.

Yogesh Sharma Over a year ago

@Avinash. . . In that case you can use APPLY.

Avinash Over a year ago

Thanks for the efforts. But it is giving me the strings after the number. Something like Was wrongly updated

Yogesh Sharma Over a year ago

@Avinash. . Ohh yes. Updated

Avinash Over a year ago

Can you please help me with this question as well . I'm looking for some clue from 2 days but couldn't get. @Yogesh

Collectives™ on Stack Overflow

Select part of the String and update in SQL Server

2 Answers 2

4 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related