CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user761758
user761758

Reputation: 585

Display only characters to the left of a special character

Using Oracle 11. We need to remove a semi colon and anything to the right of the semi colon in a set of strings.

The strings may or may not contain a semi colon. If there is no semi colon, we will return the entire string.

I can see using CASE to alter the string only if there is a semi colon, but am sure of the syntax to handle the removal of the semi colon and everything that follows the semi colon.

Strings

123456;789154 would return 123456

123456789 would return 123456789

Case
When string1 like ('%;%')
         then substr( …….)  or trim(…)

Else string1

End
As trimmedstring

Any and all help/pointers appreciated

Upvotes: 0

Views: 257

Answers (2)

Aleksej
Aleksej

Reputation: 22949

Assuming that you have to remove anything starting from the first semicolon in the string, this could be a way:

with test(s) as (
    select '123456;789154' from dual union all
    select '123456;789154;567' from dual union all
    select '123456789' from dual
)
select s, 
       case
        when instr(s, ';') = 0 then s
        else substr(s, 1, instr(s, ';')-1)
       end
from test

With regular expressions you could get the same result in a more compact, but less efficient way, with:

regexp_substr(s, '[^;]*')

Upvotes: 3

Rahul Sukhsohale
Rahul Sukhsohale

Reputation: 31

Try this:

Select LEFT(Name, Case 
       WHEN CHARINDEX(';', Name) = 0 
       then Len(Name) 
            else CHARINDEX(';', Name)-1 END) from CustomerDetails

Upvotes: -1

Related Questions