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
>

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
jgTry 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='86d87875860b03862a3e4f52e754fad
7'
and (
1=0
or ( (rel_user_group.group_id=perm_edit or perm_edit='') and (rel_user_grou
p.role_id=role_edit or role_edit='') )
or ( (rel_user_group.group_id=perm_child or perm_child='') and (rel_user_gr
oup.role_id=role_child or role_child='') )
or rel_user_group. group_id='707b826f242b92e278803c605ef81e
18'
or owner_id='86d87875860b03862a3e4f52e754fa
d7'
)
)
order by id,order_id
--8<--8<--8<--
This query sort all returning rows by order_id. order_id is generated automa
ticaly 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='86d87875860b03862a3e4f52e754fad
7'
> 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='707b826f242b92e278803c605ef81e
18'
> or owner_id='86d87875860b03862a3e4f52e754fa
d7'
> )
> )
> 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
>

Find "current database"

what is the SQL to find
1) current database
2) server name
like one can do in Oracle like:
select user from dua;
select * from v$instance;
thanks
klabu wrote:
> what is the SQL to find
> 1) current database
> 2) server name
> like one can do in Oracle like:
> select user from dua;
> select * from v$instance;
>
> thanks
SELECT DB_NAME() AS db_name;
SELECT SERVERPROPERTY('MachineName') AS MachineName;
SELECT SERVERPROPERTY('InstanceName') AS InstanceName;
SELECT SERVERPROPERTY('ServerName') AS ServerName;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||thanks
how to concat them values ?[vbcol=seagreen]
|||klabu wrote:[vbcol=seagreen]
>thanks
>how to concat them values ?
Select @.@.Servername returns current server as well.

Find "current database"

what is the SQL to find
1) current database
2) server name
like one can do in Oracle like:
select user from dua;
select * from v$instance;
thanksklabu wrote:
> what is the SQL to find
> 1) current database
> 2) server name
> like one can do in Oracle like:
> select user from dua;
> select * from v$instance;
>
> thanks
SELECT DB_NAME() AS db_name;
SELECT SERVERPROPERTY('MachineName') AS MachineName;
SELECT SERVERPROPERTY('InstanceName') AS InstanceName;
SELECT SERVERPROPERTY('ServerName') AS ServerName;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||thanks
how to concat them values ?[vbcol=seagreen]|||klabu wrote:[vbcol=seagreen]
>thanks
>how to concat them values ?
Select @.@.Servername returns current server as well.