INDEX function


INDEX function

I have a query in Teradata where I am passing a variable from SQL server through a process.


                    SELECT * FROM MyView 
                    WHERE      INDEX(   Group_ID, @GroupID ) > 0 

It transforms to 

                    SELECT * FROM MyView 
                    WHERE      INDEX(   Group_ID, ''2565','5216','5364'' ) > 0 

 It throws error

[3707] Syntax error, expected something like ',' between a string or a Unicode character literal and the integer '2565'

 If only one value is passed, for example '2565' then query works fine. I am not sure what am I missing. OR if I should use some other function besides INDEX. Basically, in simple words what I am trying to achieve is:

SELECT * FROM MyView WHERE Group_ID in (@GroupID)

Please guide.

Senior Supporter

Re: INDEX function

What is the data type of Group_ID?


From the Documentation:

Returns the position in string_expression_1 where string_expression_2 starts.
( string_expression_1 , string_expression_2 )


So you might need to exchange the order to

@Group_ID, Group_ID



'  is opening and closing a String.

So '' is a empty sting. Therefor 2565 is interpreted as Integer...

if '' is within a String it is interpreted as '



might work.

Did you consider to write your query with an IN clause?



Re: INDEX function


'''2565'',''5216'',''5364''' did not work. Data type of Group_ID is int.

IN clause was the first thing I tried. It works if I pass hard coded values, but if I pass values as variable @Group it did not work. I guess it is adding extra quotes around it. What I mean is if I try

WHERE Group_ID IN ('2445','1452'), it works, but when I pass @Group, WHERE Group_ID in (@Group), it converts to WHERE Group_ID IN (''2445','1452'') adding extra quotes and does not return anything. Can you suggest any solution to this? Or if you can suggest any other function which might work in this situation.



Senior Supporter

Re: INDEX function

maybe like will be easier...


remove all internal ' from the @Group_ID list and try

where ','!!@Group_ID!!',' like ' %, ' !! trim(Group_ID) !! ', %' "

Senior Supporter

Re: INDEX function

you can check how this works  with


select * 
from sys_calendar.calendar 
where  ',1,11,111,3212,' like '%,' !! trim(day_of_calendar) !! ',%'
Tags (1)
Teradata Employee

Re: INDEX function

I guess this is another thread parallel to


I don't think we can be any more specific until we understand what "I am passing a variable from SQL server through a process" means.  What is the process? What doe it mean that it is from SQL Server? Are you actually executing this in SQL Server?  If so, then you have to edit  @GroupID to remove the quotes as discussed previously, and construct the SQL statement as a string to be dynamically executed.