CODEWITHSUNDEEP
sqlstringfirebird
samuil
samuil

Reputation: 5081

String functions in SQL

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)

Upvotes: 0

Views: 5295

Answers (5)

José Romero
José Romero

Reputation: 462

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

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146603

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 ...

Upvotes: 2

Mark Kadlec
Mark Kadlec

Reputation: 8460

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!

Upvotes: 1

Brian Fisher
Brian Fisher

Reputation: 24009

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).

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171579

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

Upvotes: 0

Related Questions