2

My MS SQL 2019 server version is 15.0.2080.9 (X64)

I have a table like

id message
2003 ABCD-1234: some text
2897 ABCD-5678
2456 ABCD-675456: some text
3204 ABCD-4512345 :some text
4567 ABCD-2345

My requirement is to create another column in SELECT query, for each row to get like

id message key
2003 ABCD-1234: some text ABCD-1234
2897 ABCD-5678 ABCD-5678
2456 ABCD-675456 some text ABCD-675456
3204 ABCD-4512345 :some text ABCD-4512345
4567 ABCD-2345 ABCD-2345

Meaning I need from position 0 till ABCD-1234 format is satisfied discarding empty or any other text after that. I tried LEFT, RIGHT and Substring but could not find a way to get the exact length to meet the regex criteria from position 0.

Can you please help me out?

Thanks in advance

Improve this question
3
  • In this case a SUBSTRING(,message, 1, 9)might work, as it always seems to have a lenght of 9. But I suspect that won't work in the real data as length varies?? Commented Nov 9, 2022 at 7:28
  • Hi Peter, You are right, fixed length is not good for my requirement, it is a varied length of keys. Commented Nov 9, 2022 at 8:22
  • Hi Peter, I edited my question to show varied length to be clear, thanks Commented Nov 9, 2022 at 8:29

1 Answer 1

Reset to default
2

You can use PATINDEX to find the start of a LIKE pattern. You can then pass that to SUBSTRING to get the bit before that position.

Be careful as PATINDEX can return 0 which you need to null out with NULLIF

SELECT *,
  [key] =
    ISNULL(
      SUBSTRING(
        t.message,
        1,
        NULLIF(
          PATINDEX(
            '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890-]%',
            t.message
          ),
          0
        ) - 1
      ),
      t.message
    )
FROM YourTable t;

db<>fiddle

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.