find_in_set in mssql?

Hello,
i try to migrate from MySQL to MSSQL.
i have a query like
SELECT [...] FIND_IN_SET(id, "1,53,82,102,435") [...]
but MSSQL don't know the string function "FIND_IN_SET".
My question is now,
is there a FIND_IN_SET-like function for MSSQL ?
for those who not know what FIND_IN_SET do:
FIND_IN_SET(str, str_list)
str_list is a string list consiting of substrings separated by commas.
FIND_IN_SET returns the index of "str" within "str_list".
Example:
SELECT FIND_IN_SET("2", "0,1,2,3,4");
Returns: 3
Thanks in advance
jg
Try this:
Select [..] from MyTable where id in (1,53,82,102,435)
Amol.
"jg" <anonymous@.discussions.microsoft.com> wrote in message
news:7C4E7C09-5C85-44B6-ABC1-EA9F2BE20A94@.microsoft.com...
> Hello,
> i try to migrate from MySQL to MSSQL.
> i have a query like
> SELECT [...] FIND_IN_SET(id, "1,53,82,102,435") [...]
> but MSSQL don't know the string function "FIND_IN_SET".
> My question is now,
> is there a FIND_IN_SET-like function for MSSQL ?
> for those who not know what FIND_IN_SET do:
> FIND_IN_SET(str, str_list)
> str_list is a string list consiting of substrings separated by commas.
> FIND_IN_SET returns the index of "str" within "str_list".
> Example:
> SELECT FIND_IN_SET("2", "0,1,2,3,4");
> Returns: 3
> Thanks in advance
> jg
>
|||Hello Amol,
thank you for your reply, but in(...) is for the "where" clause.
FIND_IN_SET is for SELECT.
For better understanding my complete query
--8<--8<--8<--8<--
select testtable.*,username as c_user_name
,find_in_set(id,"4,5,6,48,71,2086,264,1711") as order_id
from testtable
left join auth_user on c_user=auth_user.user_id
where id in(4,5,6,48,71,2086,264,1711) and (
rel_user_group.user_id='86d87875860b03862a3e4f52e7 54fad7'
and (
1=0
or ( (rel_user_group.group_id=perm_edit or perm_edit='') and (rel_user_group.role_id=role_edit or role_edit='') )
or ( (rel_user_group.group_id=perm_child or perm_child='') and (rel_user_group.role_id=role_child or role_child='') )
or rel_user_group.group_id='707b826f242b92e278803c605 ef81e18'
or owner_id='86d87875860b03862a3e4f52e754fad7'
)
)
order by id,order_id
--8<--8<--8<--
This query sort all returning rows by order_id. order_id is generated automaticaly with the FIND_IN_SET instruction. (It contains the values 1, 2, 3, 4, ...)
Thanks for all your help.
jg
|||You can write a function that does this for you.
"jg" <anonymous@.discussions.microsoft.com> wrote in message
news:DFED7D30-FFAA-4D97-B49F-A2EC285BF762@.microsoft.com...
> Hello Amol,
> thank you for your reply, but in(...) is for the "where" clause.
> FIND_IN_SET is for SELECT.
> For better understanding my complete query
> --8<--8<--8<--8<--
> select testtable.*,username as c_user_name
> ,find_in_set(id,"4,5,6,48,71,2086,264,1711") as order_id
> from testtable
> left join auth_user on c_user=auth_user.user_id
> where id in(4,5,6,48,71,2086,264,1711) and (
> rel_user_group.user_id='86d87875860b03862a3e4f52e7 54fad7'
> and (
> 1=0
> or ( (rel_user_group.group_id=perm_edit or perm_edit='') and
(rel_user_group.role_id=role_edit or role_edit='') )
> or ( (rel_user_group.group_id=perm_child or perm_child='') and
(rel_user_group.role_id=role_child or role_child='') )
> or rel_user_group.group_id='707b826f242b92e278803c605 ef81e18'
> or owner_id='86d87875860b03862a3e4f52e754fad7'
> )
> )
> order by id,order_id
> --8<--8<--8<--
> This query sort all returning rows by order_id. order_id is generated
automaticaly with the FIND_IN_SET instruction. (It contains the values 1, 2,
3, 4, ...)
> Thanks for all your help.
> jg
>