SQL Select Expression Substitution

Question

I have a number of links with a length of 20 and I need to remove the 1st 12 numbers, replace with G and select the next 7 numbers

An example of the format of accepted numbers

50125426598525412584

Then I need to remove the first 12 digits and select the next 7 (not including the last)

Finally, I need to put a G in front of the number so that I stay with

G25412584

My SQL looks like this:

SELECT SUBSTRING(ref, 12, 7) AS ref 
FROM mytable
WHERE ref LIKE '5012%'

The results of this will leave me with

25412584

But how do you insert G

before the number in the same SQL statement?

Many thanks

+3

Gselect 09 May '15 at 21:30

2 answers

Gabriel · Answer 1 · 2015-05-09T21:34:05+0000

SELECT 'G'+SUBSTRING(ref, 12, 7) AS ref FROM mytable where ref like '5012%'

Rajesh · Answer 2 · 2015-05-09T21:43:00+0000

SELECT CONCAT( 'G', SUBSTRING('50125426598525412584', 13,7)) from dual;