Problem using REPLACE

[Arif15]

Hello, I have to change codes in my Oracle database: I have to extract the codes beginning with 02 and having a length of 4 and 6 characters of the 59,350 community. Once extracted I must replace 02 of the top 3 by letters LVB. The problem with this request and if for example a 4 digit code is '0202'; the request will turn into 'LVBLVB' but I want it as 'LVB02'.

The query works for codes 02XX or type 02XXXX, X is different from '02 'and between 2 or 4 characters.

SELECT REPLACE (or.CODE, '02', 'LVB')
FROM ORGANIZATION or
WHERE or.COL = '59350'
AND SubStr(or.CODE, 1, 2) = '02'
AND (or.LGCODE = '4' OR or.LGCODE = '6')

I tried in the first row to SELECT REPLACE (or.CODE, '02%', 'LVB') the percentage here is not caught in the LIKE expression. So I am stuck.

[XSI]

SELECT 'LVB' || substr(OR.CODE,3,4000)
FROM ORGANIZATION OR
WHERE OR.COL = '59350'
AND SubStr(OR.CODE, 1, 2) = '02'
AND (OR.LGCODE = '4' OR OR.LGCODE = '6')

Personally, I would have avoided using the alias "OR"

[CodGuru]

I have an example that can do what you want, here I am replacing the first character of a postal code by another without using REPLACE:

SELECT ad.adr_cod_pos, '6' || substr(ad.adr_cod_pos, 2, length(ad.adr_cod_pos) - 1) 
FROM address AD 
WHERE ad.adr_cod_pos LIKE '5%'

If you want to adapt, I think that by

SELECT or.CODE, 'LVB' || substr(or.CODE, 3, length(or.CODE) - 2) 
FROM ORGANIZATION or 
WHERE or.COL = '59350' 
AND SubStr(or.CODE, 1, 2) = '02' 
AND (or.LGCODE = '4' OR or.LGCODE = '6')

[Arif15]

Thank you for your responses. By the way XSI, why would you recommend me to not to use "or" alias? Would it clash with logical "OR" in SQL and thus generating an error message to me? In such case, I think I will go for "org" simply because as you might know aliases makes the query look quiet understandable.