CODEWITHSUNDEEP
sqlregexstringoracle-databasesql-update
Ivan the Smurf
Ivan the Smurf

Reputation: 95

how to replace substring at the end of string using SQL?

I have data in a table like this:

xxx-xxxxxxx-29
xxx-xxxxxx-87
xxx-xxxxxxxxx-2
xxx-xxx-33

I need to replace every -n at the end with -0n (to add a zero between the dash and the number). Leave -nn as it is.

So, if 2nd character from the right is '-' replace it with '-0'

can this be done using UPDATE?

Upvotes: 3

Views: 871

Answers (2)

GMB
GMB

Reputation: 222472

I need to replace every -n at the end with -0n (to add a zero between the dash and the number).

One option is to use regexp_replace():

update mytable set mycol = regexp_replace(mycol, '-(\d)$', '-0\1');

'-(\d)$' matches on a dash followed by a one digit at the end of the string, and captures the digit; you can then prepend a leading '0'.

Upvotes: 3

Tony Andrews
Tony Andrews

Reputation: 132580

Here is one way:

update mytable
set data = substr(data, 1, length(data)-1) || '0' || substr(data,-1)
where data like '%-_';

Upvotes: 2

Related Questions