All Forums Database
twifosp 1 post Joined 02/09
27 Feb 2009
Substring in where statement

I am trying to use a substring function in a where statement. [code]select top 1 * from tablewhere 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.

Fred 1096 posts Joined 08/04
27 Feb 2009

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)

You must sign in to leave a comment.