All Forums Database
deagle 17 posts Joined 11/08
07 Jan 2009
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

Fred 1096 posts Joined 08/04
07 Jan 2009

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

deagle 17 posts Joined 11/08
08 Jan 2009

That worked a treat. Thanks for your help

08 Jan 2009

Nice logic fred.

raj786 23 posts Joined 04/14
26 Sep 2014

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.

dnoeth 4628 posts Joined 11/04
27 Sep 2014

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)

 

Dieter

sakthikrr 53 posts Joined 07/12
25 Aug 2015

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
 

Sakthi

dnoeth 4628 posts Joined 11/04
26 Aug 2015

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)+)')

 

Dieter

sakthikrr 53 posts Joined 07/12
27 Aug 2015

Thanks Dieter! Thats perfect solution!

Sakthi

DivyaK 2 posts Joined 03/16
17 Mar 2016

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

sakthikrr 53 posts Joined 07/12
17 Mar 2016

Just use case to handle values with absence of 'x' as shown below:

case when POSITION('-' IN myField) <> 0 then SUBSTRING(myField FROM 1 FOR POSITION('-' IN myField)-1)
else NULL end

Hope this helps!

Sakthi

DivyaK 2 posts Joined 03/16
18 Mar 2016

Hi Sakthi,
CASE WHEN POSITION ('-' IN myField) <> 0 THEN SUBSTRING(myField FROM POSITION ('-' IN myField)+1 FOR LENGTH(myField)) 
 
ELSE NULL END
 
This has worked for me
 
Thanks

You must sign in to leave a comment.