CODEWITHSUNDEEP
sqlteradata
Tai
Tai

Reputation: 85

strip string after character

Hello i have a column with a string value that looks like test ( test):5 and i want to strip everything after : including this character.

I have the below sql and keep getting this error:

[Teradata Database] [TeraJDBC 14.10.00.26] [Error 3706] [SQLState 42000] Syntax error: expected something between '(' and the string ':'. 

select blah, LEFT(blah, CHARINDEX(':', blah) - 1) AS newblah from blah_table

can someone please help?

thanks

Upvotes: 0

Views: 3084

Answers (3)

dnoeth
dnoeth

Reputation: 60462

Teradata doesn't support the LEFT string function before 15.10, but this must not be negative. CHAR_INDEX is SQL Server proprietary syntax, POSITION is Standard SQL/Teradata:

-- will fail if there's no ':' because FOR must be >= 0
SUBSTRING(blah FROM 1 FOR (POSITION('0' in blah) - 1))

-- will work because FROM might be negative, too 
SUBSTRING(blah FROM 0 FOR (POSITION('0' in blah)))

What do you want to return if there's no ':'?

The SUBSTRING will return an empty string, if you want the full string instead better use:

-- tokenize the string and return the 1st token
STRTOK(blah, ':', 1)

Upvotes: 2

Rob Paller
Rob Paller

Reputation: 7786

I believe this will work for you on Teradata:

SELECT SUBSTRING(blah FROM 1 FOR (POSITION(';' in blah) - 1));

Upvotes: 0

J Greene
J Greene

Reputation: 361

I think this is right ... at least in sql server ... not sure about teradata.

SELECT  SUBSTRING(blah, 0, LEN(blah) - CHARINDEX (':', blah))

Upvotes: 0

Related Questions