Remove only first character from the field if it is 0-CodePudding

How to write sql query which will show values skipping first character if it is 0 (only the first character). All values are 3 characters long. Examples:

numbers
123
023
003
102

should display as follows (after executing the query)

numbers
123
23
03
102

I used the following solution, but it removes all 0's, not just the first. How to fix it so that it only removes the first character if it is 0.

SUBSTRING(numbers, PATINDEX('%[^0]%', numbers '.'), LEN(numbers))

I will be grateful for your help.

CodePudding user response：

You can use CASE expression:

SELECT CASE WHEN LEFT(numbers, 1) = '0' THEN RIGHT(numbers, 2) ELSE numbers END AS FormattedNumbers

CodePudding user response：

why not using simple substr() ?

select case when substr(mycol,1,1)='0' then substr(mycol,2) else mycol end
from my table

you did not mention your DB so i assumed its oracle. This will work in any RDBMS.

CodePudding user response：

You can use charindex and substring methods to do string manipulation :)

select
    case when charindex('0', number) = 1
        then substring(number, 2, len(number))
        else number end
from (
    select '123' number
    union all
    select '023'
    union all 
    select '003'
    union all
    select '102'
) a