CODEWITHSUNDEEP
mysqlsqldatabase
Moses
Moses

Reputation: 29

SQL query to to add a character to a string

I need help formulating a somewhat complexed SQL query.

I have a DB-table called 'models' with a column called 'modelName'.

The values in modelName look typically like this: B1-01-A-1 or B2-01-A-1 or A-01-A-1

Now, to all the strings which start with B1 or B2 I want to insert a 0 before the last position. So B1-01-A-1 will become B1-01-A-01 B2-01-A-1 will become B2-01-A-01 and so on.

However, those which start with just a letter, like A-01-A-1, will remain without change.

So briefly:

B1-01-A-1 >> B1-01-A-01

C1-04-B-2 >> C1-04-B-01

G1-02-D-3 >> G1-02-D-03

etc.

But:

A-01-A-1 >> A-01-A-1 (no change)

A-01-A-2 >> A-01-A-2 (no change)

etc.

Thanks in advance for your help!

Ishai

Upvotes: 1

Views: 60

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

If you are using MySQL 8+, we can try doing a regex replacement here:

SELECT
    modelName,
    REGEXP_REPLACE(modelName, '^([A-Z]+\\d+-.*)-(\\d)$', '$1-0$2')
        AS newModelName
FROM yourTable;

screen capture from demo link below

Demo

Here is an explanation of the regex pattern being used:

  • ^ from the start of the model name
  • ( capture in $1
    • [A-Z]+ a leading capital letter(s)
    • \d+ also followed by a number
    • - dash separator
    • .* all following content
  • ) close $1
  • - dash separator
  • (\d) match and capture in $2 a single digit
  • $ end of the model name

Then we build the output as $1-0$2, prepending a zero before the final single digit.

Upvotes: 3

Related Questions