Search code examples
mysqlsqlrowsdata-cleaning

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.

Solution

  • 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)