Remove all characters after a specific character in PL / SQL

Question

How to get a substring from this example value:

john.abc_1234 

I want him to return john.abc . So basically we need to delete all the information after _ .

Other examples: 1234_abc

Answer 1

You can use SUBSTR and INSTR :

 select substr('john.abc_1234', 1, instr('john.abc_1234', '_') -1) from dual 

A warning. This is only guaranteed if your string has an underscore in it.

Update

In addition, if you are working with Oracle 10g, you can use the Regex path, which will handle exceptions more efficiently.

Here are some links on how to do this in Oracle:

• http://psoug.org/reference/regexp.html
• http://psoug.org/snippet/Regular-Expressions--Regexp-Cheat-Sheet_856.htm
• http://www.regular-expressions.info/oracle.html

Answer 2

You can use this monster if you are not sure that all your values ​​contain the character that you want to cut the string.

 SELECT (CASE WHEN INSTR(field, '_') > 0 THEN substr(field, 1, instr(field, '_') -1) ELSE field END) AS field FROM dual 

Answer 3

This guy got it! http://programcsharp.com/blog/post/strip-non-numeric-characters-from-a-string-in-sql-server

 SELECT (SELECT CAST(CAST(( SELECT SUBSTRING(FieldToStrip, Number, 1) FROM master..spt_values WHERE Type='p' AND Number <= LEN(FieldToStrip) AND SUBSTRING(FieldToStrip, Number, 1) LIKE '[0-9]' FOR XML Path('')) AS xml) AS varchar(MAX))) FROM SourceTable