Remove characters after '-' in a string

Database
Enthusiast

Remove characters after '-' in a string

Hi all,

I have the following string '10041477/2008/021-2 '. What I want to do is remove the characters after the '-'. The field is defined as CHAR(20). There is not always 17 places before the '-'. Is there a way to do this? I have tried substring but can't seem to figure it out.

Thanks in advance
12 REPLIES
Teradata Employee

Re: Remove characters after '-' in a string

SUBSTRING(myField FROM 1 FOR POSITION('-' IN myField)-1)
Enthusiast

Re: Remove characters after '-' in a string

That worked a treat. Thanks for your help
Enthusiast

Re: Remove characters after '-' in a string

Nice logic fred.
Enthusiast

Re: Remove characters after '-' in a string

Hi All,

I have the following date format in my source 2014-09-21 00:00:00.7563362.

my datatype  in teradata is  timestamp(6). i need o/p like  2014-09-21 00:00:00.756336.

Is there a way to do this?is it possible to keep all 7 digit as timestamp format?

thanks in adv.

Senior Apprentice

Re: Remove characters after '-' in a string

Teradata Timestamps only support 6 digits, so you must strip of the last digit.

Do you really have data with that accuracy?

In your case there's a fixed format, so simply extracting the first 26 characters should work:

substring(x from 1 FOR 26)
Enthusiast

Re: Remove characters after '-' in a string

Dear all,

I have few order numbers like '1-1234-1' and I need to display them as '1-1234'; If I use substring and position as shown below then I'm getting '1' only. 

sel SUBSTRING('1-1234-1' FROM 1 FOR POSITION('-' IN '1-1234-1')-1)

Is there a way to remove the string from second '-'?

Thanks in advance!

Sakthi

Senior Apprentice

Re: Remove characters after '-' in a string

Hi Sakthi,

switch to either INSTR instead of position or REGEXP_SUBSTR:

SUBSTRING('1-1234-1' FROM 1 FOR instr('1-1234-1''-', '-', 1, 2)-1) -- find the 2nd dash

regexp_substr('1-1234-1', '((\d)+-(\d)+)')
Enthusiast

Re: Remove characters after '-' in a string

Thanks Dieter! Thats perfect solution!

Fan

Re: Remove characters after '-' in a string

Hi All,

I want to print the values after 'x' in the given column. But, I have few values where 'x' is not present. Those cases should return null. When I tried the below syntax  

SUBSTRING(myField FROM 1 FOR POSITION('-' IN myField)-1)

below error was thrown

SELECT Failed: [2663] SUBSTR: string subscript out of bounds in *tablename*

Please help