Skip to content
Advertisement

Omit certain characters from Rows in SQL

I have a column that has Serial Numbers but some of them has unwanted characters attached with the Serial Number:-

8009914407
MCO
8558201722
US2
US3
MCO
7272950703
US2
MCO
NULL
8558201722
7272505721_R2C
8002094684
8558088363_AAC
NULL
US2

These are some of the entries from a Column in a SQL Table.

I want to omit all the characters including and after the Underscore. For example, these values would become this:-

8558088363_AAC = 8558088363

7272505721_R2C = 7272505721

And keep rest of the rows as is.

Advertisement

Answer

You an use substring_index():

select substring_index(serial_number, '_', 1)

Here is a db<>fiddle.

In SQL Server, the code is:

select left(serial_number, charindex('_', serial_number + '_') - 1)
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement