Extract certain part of an ID Using SQL

Question

I have a column called ID which I only want to extract portion of the ID.

For example:

ID
---------
XX00ABCDE
XX1067HJK
XX78YUTIO
XX00NNYUT
XX56PBPHY

I do not want the initial XX and any leading 0 after the XX.

The result I want would be:

ExtractID
---------
ABCDE
1067HJK
78YUTIO
NNYUT
56PBPHY

How might I accomplish this using SQL?

Thank you in advance for your help.

Answer 1

Examples

SELECT REPLACE('abcdefghicde','cde','xxx');

source

in your example:

SELECT REPLACE(ID,'XX','') FROM TBALE_NAME

Answer 2

It's not pretty, but if you have specific values (XX) and you know you have no more than two "0's" after the "XX" then the following will work. If you sometimes have three, four or more "0's" then you would just keep repeating this pattern with the longer strings being the innermost replace statements.

SELECT
  Replace(Replace(Replace(T.ID,'XX00',''),'XX0',''),'XX','')
  AS ExtractID
FROM Table T

Again, it's not pretty, but it should do what you want.

Answer 3

if you string are always formated like this :

XX + NUM + ALPHA_UPPER you can do it

with tmp as (
select id, 
trim(translate(replace(ID, 'XX', ''), '          ', '0123456789')) idalpha, 
trim(translate(ID, '                          ', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')) IDNUM
from  yourlib.yourtable
)
select id, 
ifnull(cast(nullif(cast(case when IDNUM='' then 0 else IDNUM end as integer), 0) as varchar(10)), '') || idalpha as NewID
from tmp