substring or trim a text string up to a specific character

Database
Enthusiast

substring or trim a text string up to a specific character

I am trying to extract only parts of text strings that are of varying length but in each case I am trying to only get only the part of the string that preceeds a specific character.   For example for the string  “one1_YYY” I would want only the “one1” and in “second2-VVV_YYY” I only want “second2-VVV”

To my knowledge substring and trim only work on specific character positions.    Does anyone know of any way to do this?

Thanks

R

3 REPLIES
Junior Contributor

Re: substring or trim a text string up to a specific character

SUBSTRING(col FROM 1 FOR POSITION('_' IN col) - 1)

Dieter

New Member

Re: substring or trim a text string up to a specific character

Or you could use REGEXP_REPLACE

 

SyntaxEditor Code Snippet

SELECT RegExp_Replace(column_name, '^(.*)_.*', '\1', 1, 0, 'i') FROM table_name;

And if you want everything AFTER a charachter 

SELECT RegExp_Replace(column_name, '^.*_(.*)', '\1', 1, 0, 'i') FROM table_name;

 

For example if you wanted only the domain names from email address

  • some@web.domain
  • some@email.fake
  • some@another.web.site

 

SELECT RegExp_Replace(email, '^.*@(.*)', '\1', 1, 0, 'i') FROM emails;

 

  • web.domain
  • email.fake
  • another.web.site

 

So you could even prepend the domains with the prorocol to get something like http://another.web.site

SELECT RegExp_Replace(email, '^.*@(.*)', 'http://\1', 1, 0, 'i') FROM emails;
Junior Contributor

Re: substring or trim a text string up to a specific character

This RegExp_Substr also works,

RegExp_Substr(Column_Name, '^.*(?=_)')

But for this simple case a Regex is way more expensive than a substring or

StrTok(Column_Name, '_', 1)