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

Reputation: 31

Substring SQL Select statement

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

Upvotes: 3

Reputation: 2155

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

Upvotes: 1

Reputation: 3584

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

Upvotes: 3