CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Sanjay Jain
Sanjay Jain

Reputation: 3587

How to Select a substring in Oracle SQL for specific length only

I am having below values from the column

Length of first value is 48.

Length of second value is 97.

Now condition is if the length is 97 of any value then use first 48 characters only else use what ever exists.

A SUBSTR function is already there 

SELECT SUBSTR(CLNT_ID,0,48) FROM CLNT;

which is working fine, Now I want to use this only in case where the length of value is equal to 97.

This Select statement is part of one store procedure. Please assist to identify right query for this. Thanks in advance

Upvotes: 1

Views: 6998

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270001

I think the simplest way is to use length():

select (case when length(clnt_id) = 97
             then substr(clnt_id, 1, 48)
             else clnt_id
        end)
from clnt;

This would seem to be the simplest implementation of the logic. Also note that the second argument for substr() counts from "1" not "0", so you should use "1" there (the effect is the same).

Upvotes: 3

eifla001
eifla001

Reputation: 1157

You can also try this way,

SELECT CASE SIGN( LENGTH(:p_text) - 97 )
            WHEN -1 THEN
                :p_text
            ELSE
                SUBSTR(:p_text, 1, 48)
       END
  FROM dual;

Upvotes: 1

Paweł Prusinowski
Paweł Prusinowski

Reputation: 416

SELECT 
case 
  when regexp_like (CLNT_ID , '^[[:alnum:]]{97}$') 
  then SUBSTR(CLNT_ID,0,48) 
  else null 
end label1, CLNT_ID
FROM CLNT;

Upvotes: 2

Related Questions