Substring SQL Select statement

Question

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

An example of the format of the numbers being received

50125426598525412584

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

Lastly I need to put a G in front of the number so I'm left with

G25412584

My SQL is as follows:

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 I insert the G in front of the number in the same SQL statement?

Many thanks

Gabriel · Accepted Answer · 2015-05-09 21:34:05Z

3

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

answered May 9, 2015 at 21:34

Gabriel

3,6041 gold badge29 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gabriel, really appreciate your response. I am trying to do an insert based on the results of the select but getting a

what do you have exactly? and does the row actually exist?

Rajesh · Accepted Answer · 2015-05-09 21:43:00Z

1

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

answered May 9, 2015 at 21:43

Rajesh

2,1551 gold badge14 silver badges14 bronze badges