0
votes

I have a table with field ID.

Here are some sample values:

  1. 0001
  2. 0002
  3. 990003
  4. 990004
  5. 880005
  6. 880006
  7. 888006

I have a point where im fetching this records and i need to cut out the 99/88 (well its a pre-known prefix in my case its either 99 or 88 ... keep in mind that a valid id is also lets say 888888 that needs to be converted to 8888!.

I am aware that if all records had leading 99 or 88 i could use:

SELECT RIGHT(MyColumn, LEN(MyColumn) - 2) AS MyTrimmedColumn

How would i include a condition if there is leading 99/88 do the trim else not?

The expected output would be:

  1. 0001
  2. 0002
  3. 0003
  4. 0004
  5. 0005
  6. 0006
  7. 8006

The function should be part of the select query and not a standalone query (example of the problem - field ID)!

SELECT
ISNULL(ProcId,0) ProcId,
ISNULL(ID,'') ID,
...
2
Please add the expected output.TechDo
@techdo: The expected output is clear. Maybe you should re-read the question?Daniel Hilgarth

2 Answers

4
votes
SELECT case when mycolumn >= 880000 
            then RIGHT(MyColumn, LEN(MyColumn) - 2) 
            else mycolumn
       end AS MyTrimmedColumn

or

SELECT case when mycolumn between 880000 and 889999
            then MyColumn - 880000 
            when mycolumn between 990000 and 999999
            then MyColumn - 990000 
            else mycolumn
       end AS MyTrimmedColumn
0
votes
SELECT RIGHT(MyColumn, LEN(MyColumn) - 2) AS MyTrimmedColumn from table-name 
where id like '99%'