String functions in SQL

Question

I need query (to be more specific, it is view, not query) that will return string column. String will be code, that might be prefixed with two letters (LJ). If it is prefixed -- prefix have to be trimmed. If there is no prefix present, it should be left untouched. Is it possible to do such thing in SQL? Server is FirebirdSQL 1.5.

---

None of given solutions worked, but with their help, I was able to figure it out by myself. Substring function does exist, but has different syntax. Instead of commas you have to use proper keywords:

Substring(col from pos for count)

Answer 1

SELECT
  CASE Substring(columnName, 2, 2) IS 'LJ' THEN Subsrtring(columnName, 2, LEN(columnName) -2) ELSE columnName END

That's what I think you are looking for, haven't executed to test but you get the gist...

Hope it helps!

Answer 2

yes, instead of selecting the column itself, write an expression that replaces the prefix LJ with nothing

   Select case When colName Like 'LJ%' 
                Then SubString([colName], 2, Len(colName) - 2)
                Else ColName End
   From ...

Answer 3

select case 
           when substring(MyColumn, 1, 2) = 'LJ' 
               then substring(MyColumn, 3, len(MyColumn) - 2) 
           else 
               MyColumn 
       end

Answer 4

You could do it with string manipulation:

SELECT CONCAT(REPLACE(LEFT(column_name,2), 'LJ', ''), SUBSTRING(column_name, 3))

Not sure this would work in Firebird (tested on Mysql).

Answer 5

Get a copy of FreeUDFLib, it have a LOT of functions for strings, math, date, convertions, blob handling, etc. It will do what you want, an a lot more of things