Substring in where statement

Database

Substring in where statement

I am trying to use a substring function in a where statement.

[code]
select top 1 *
from table
where column = substring('B16CDB7DEE274638B16FAA6F287C7956',1,8)||'-'
|| substring('B16CDB7DEE274638B16FAA6F287C7956',9,4) || '-'
|| substring('B16CDB7DEE274638B16FAA6F287C7956',13,4) || '-'
|| substring('B16CDB7DEE274638B16FAA6F287C7956',21,13)
[/code]

This returns a syntax error of: expected something between string and ','

But if i

[code]
select substring('B16CDB7DEE274638B16FAA6F287C7956',1,8)||'-'
|| substring('B16CDB7DEE274638B16FAA6F287C7956',9,4) || '-'
|| substring('B16CDB7DEE274638B16FAA6F287C7956',13,4) || '-'
|| substring('B16CDB7DEE274638B16FAA6F287C7956',21,13)
[/code]

It returns B16CDB7D-EE27-4638-B16FAA6F287C7956

If it works in a plain select statement, why can't I use it as part of a where clause?

Basically I have a table with a bunch of GUIDs in the above dashless format that I need to join to a table with the dash format and this is my attempt at testing the substring code that will be a part of the join operator.
1 REPLY
Teradata Employee

Re: Substring in where statement


That's not the correct syntax for the Teradata "substring" function. I suspect you are using ODBC (e.g. SQL Assistant) and allowing it to parse the query. When you put the function in the SELECT list the driver can do the processing on the client side and make it appear to be OK, but it can't do that in a WHERE clause.

You need to either use either of the correct syntax variants for "substring", e.g.
substr(textcol,4,2)
substring(textcol FROM 4 FOR 2)