In(@variable) clause and TABLE datatype variable

Hi this question has been asked several times and some solution has been
provided already. But the one I am facing is with a twist. I need to use the
IN() clause with a variable as its parameter. The variable is a list of comm
a
separated character values all enclosed in pairs of single quotes. I could
have solved this problem by enclosing the final query in a single quote and
running Exec command on it (with the Variable list outside the quotes) but I
also need to use a Table data type variable which raises error when EXEC
command is run.
Followig is the example that may explain well.
I have oversimplified this example and it does things that we would not do
in normal situation
use pubs;
-- declare and set Table variable
declare @.TableVariable TABLE ( col char(4) );
INSERT @.TableVariable
Select pub_id FROM publishers
;
--declare and set CSV single quoted characters list
declare @.ListVariable varchar(100);
set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
publishers table, promise
--the query where the Table variable is used as well as the IN() clause is
used
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN (@.ListVariable)
-- returns 0 rows
--if we use Exec by replacing the last code section above with as following
declare @.command varchar(2000)
set @.command='
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN ('+@.ListVariable+')
'
exec (@.command)
--Then we get the error message:
-- Must declare the variable '@.TableVariable'.
Expand AllCollapse All
Manage Your Profile |Legal |Contact Us |MSDN Flash Newsletterhttp://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--
"Aamir Ghanchi" <AamirGhanchi@.discussions.microsoft.com> wrote in message
news:D82370C5-6E5E-4178-98B8-0017FE88E809@.microsoft.com...
> Hi this question has been asked several times and some solution has been
> provided already. But the one I am facing is with a twist. I need to use
> the
> IN() clause with a variable as its parameter. The variable is a list of
> comma
> separated character values all enclosed in pairs of single quotes. I could
> have solved this problem by enclosing the final query in a single quote
> and
> running Exec command on it (with the Variable list outside the quotes) but
> I
> also need to use a Table data type variable which raises error when EXEC
> command is run.
> Followig is the example that may explain well.
> I have oversimplified this example and it does things that we would not do
> in normal situation
> use pubs;
> -- declare and set Table variable
> declare @.TableVariable TABLE ( col char(4) );
> INSERT @.TableVariable
> Select pub_id FROM publishers
> ;
> --declare and set CSV single quoted characters list
> declare @.ListVariable varchar(100);
> set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
> publishers table, promise
> --the query where the Table variable is used as well as the IN() clause is
> used
> Select TableVariable.col
> From @.TableVariable as TableVariable
> Where TableVariable.col IN (@.ListVariable)
> -- returns 0 rows
> --if we use Exec by replacing the last code section above with as
> following
> declare @.command varchar(2000)
> set @.command='
> Select TableVariable.col
> From @.TableVariable as TableVariable
> Where TableVariable.col IN ('+@.ListVariable+')
> '
> exec (@.command)
> --Then we get the error message:
> -- Must declare the variable '@.TableVariable'.
>
> Expand AllCollapse All
>
> Manage Your Profile |Legal |Contact Us |MSDN Flash Newsletter
>

IN(@variable) clause and Table Data Type variable

Hi this question has been asked several times and some solution has been
provided already. But the one I am facing is with a twist. I need to use the
IN() clause with a variable as its parameter. The variable is a list of comm
a
separated character values all enclosed in pairs of single quotes. I could
have solved this problem by enclosing the final query in a single quote and
running Exec command on it (with the Variable list outside the quotes) but I
also need to use a Table data type variable which raises error when EXEC
command is run.
Followig is the example that may explain well.
I have oversimplified this example and it does things that we would not do
in normal situation
use pubs;
-- declare and set Table variable
declare @.TableVariable TABLE ( col char(4) );
INSERT @.TableVariable
Select pub_id FROM publishers
;
--declare and set CSV single quoted characters list
declare @.ListVariable varchar(100);
set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
publishers table, promise
--the query where the Table variable is used as well as the IN() clause is
used
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN (@.ListVariable)
-- returns 0 rows
--if we use Exec by replacing the last code section above with as following
declare @.command varchar(2000)
set @.command='
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN ('+@.ListVariable+')
'
exec (@.command)
--Then we get the error message:
-- Must declare the variable '@.TableVariable'.
Expand AllCollapse All
Manage Your Profile |Legal |Contact Us |MSDN Flash NewsletterI'd suggest using something like this. in (select id from tablname)
in your dynamic SQL example don't use the variable.
Set @.command = ' Select TableVariable.col
>From @.TableVariable as TableVariable
Where TableVariable.col IN ('
loop on list
select @.command = @.command + each number
End
select @.command = @.command + ')'
exec (@.command)|||Use a temp table instead.
David Gugick
Imceda Software
www.imceda.com|||http://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--
"Aamir Ghanchi" <AamirGhanchi@.discussions.microsoft.com> wrote in message
news:EAE233F0-A1C4-4CEF-9F72-8EB4EB4884E2@.microsoft.com...
> Hi this question has been asked several times and some solution has been
> provided already. But the one I am facing is with a twist. I need to use
> the
> IN() clause with a variable as its parameter. The variable is a list of
> comma
> separated character values all enclosed in pairs of single quotes. I could
> have solved this problem by enclosing the final query in a single quote
> and
> running Exec command on it (with the Variable list outside the quotes) but
> I
> also need to use a Table data type variable which raises error when EXEC
> command is run.
> Followig is the example that may explain well.
> I have oversimplified this example and it does things that we would not do
> in normal situation
> use pubs;
> -- declare and set Table variable
> declare @.TableVariable TABLE ( col char(4) );
> INSERT @.TableVariable
> Select pub_id FROM publishers
> ;
> --declare and set CSV single quoted characters list
> declare @.ListVariable varchar(100);
> set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
> publishers table, promise
> --the query where the Table variable is used as well as the IN() clause is
> used
> Select TableVariable.col
> From @.TableVariable as TableVariable
> Where TableVariable.col IN (@.ListVariable)
> -- returns 0 rows
> --if we use Exec by replacing the last code section above with as
> following
> declare @.command varchar(2000)
> set @.command='
> Select TableVariable.col
> From @.TableVariable as TableVariable
> Where TableVariable.col IN ('+@.ListVariable+')
> '
> exec (@.command)
> --Then we get the error message:
> -- Must declare the variable '@.TableVariable'.
>
> Expand AllCollapse All
>
> Manage Your Profile |Legal |Contact Us |MSDN Flash Newsletter
>|||On Mon, 7 Feb 2005 14:31:03 -0800, Aamir Ghanchi wrote:
(snip)
I have already answered this question in .server, even before you posted
it (twice!) to this group. Please don't multi-post.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Good thought. and this is what I did initially. but my IN() clause is
enclosed within the aggregate function SUM and subquery is not allowed in it
.
Thanks though.
"Paul Moore" wrote:

> I'd suggest using something like this. in (select id from tablname)
>
> in your dynamic SQL example don't use the variable.
> Set @.command = ' Select TableVariable.col
> Where TableVariable.col IN ('
> loop on list
> select @.command = @.command + each number
> End
> select @.command = @.command + ')'
> exec (@.command)
>|||slow though, but I guess thats the only viable option I am left with.
Thank you.
"David Gugick" wrote:

> Use a temp table instead.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||I understand. I just started using MSDN website for posting messages since
the new Google Groups interface eats up all the indentation on code snippets
.
Bad thing, I can't crosspost through MSDN site :(
thanks.
"Hugo Kornelis" wrote:

> On Mon, 7 Feb 2005 14:31:03 -0800, Aamir Ghanchi wrote:
> (snip)
> I have already answered this question in .server, even before you posted
> it (twice!) to this group. Please don't multi-post.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||>> but my IN() clause is enclosed within the aggregate function SUM
and subquery is not allowed in it. <<
What did you think that the arithmetic sum of a logical expression
would be anyway? Think about it for two seconds.

IN(@ListVariable) and TABLE Data Type

Hi this question has been asked several times and some solution has been
provided already. But the one I am facing is with a twist. I need to use the
IN() clause with a variable as its parameter. The variable is a list of comma
separated character values all enclosed in pairs of single quotes. I could
have solved this problem by enclosing the final query in a single quote and
running Exec command on it (with the Variable list outside the quotes) but I
also need to use a Table data type variable which raises error when EXEC
command is run.
Followig is the example that may explain well.
I have oversimplified this example and it does things that we would not do
in normal situation
use pubs;
-- declare and set Table variable
declare @.TableVariable TABLE ( col char(4) );
INSERT @.TableVariable
Select pub_id FROM publishers
;
--declare and set CSV single quoted characters list
declare @.ListVariable varchar(100);
set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
publishers table, promise
--the query where the Table variable is used as well as the IN() clause is
used
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN (@.ListVariable)
-- returns 0 rows
--if we use Exec by replacing the last code section above with as following
declare @.command varchar(2000)
set @.command='
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN ('+@.ListVariable+')
'
exec (@.command)
--Then we get the error message:
-- Must declare the variable '@.TableVariable'.
On Mon, 7 Feb 2005 13:27:06 -0800, "aamirghanchi@.yahoo.com"
<aamirghanchi@.yahoo.com@.discussions.microsoft.com> wrote:

> I need to use the
>IN() clause with a variable as its parameter. The variable is a list of comma
>separated character values all enclosed in pairs of single quotes. I could
>have solved this problem by enclosing the final query in a single quote and
>running Exec command on it (with the Variable list outside the quotes) but I
>also need to use a Table data type variable which raises error when EXEC
>command is run.
Hi aamirqhanchi,
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

IN(@ListVariable) and TABLE Data Type

Hi this question has been asked several times and some solution has been
provided already. But the one I am facing is with a twist. I need to use the
IN() clause with a variable as its parameter. The variable is a list of comm
a
separated character values all enclosed in pairs of single quotes. I could
have solved this problem by enclosing the final query in a single quote and
running Exec command on it (with the Variable list outside the quotes) but I
also need to use a Table data type variable which raises error when EXEC
command is run.
Followig is the example that may explain well.
I have oversimplified this example and it does things that we would not do
in normal situation
use pubs;
-- declare and set Table variable
declare @.TableVariable TABLE ( col char(4) );
INSERT @.TableVariable
Select pub_id FROM publishers
;
--declare and set CSV single quoted characters list
declare @.ListVariable varchar(100);
set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
publishers table, promise
--the query where the Table variable is used as well as the IN() clause is
used
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN (@.ListVariable)
-- returns 0 rows
--if we use Exec by replacing the last code section above with as following
declare @.command varchar(2000)
set @.command='
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN ('+@.ListVariable+')
'
exec (@.command)
--Then we get the error message:
-- Must declare the variable '@.TableVariable'.On Mon, 7 Feb 2005 13:27:06 -0800, "aamirghanchi@.yahoo.com"
<aamirghanchi@.yahoo.com@.discussions.microsoft.com> wrote:

> I need to use the
>IN() clause with a variable as its parameter. The variable is a list of com
ma
>separated character values all enclosed in pairs of single quotes. I could
>have solved this problem by enclosing the final query in a single quote and
>running Exec command on it (with the Variable list outside the quotes) but
I
>also need to use a Table data type variable which raises error when EXEC
>command is run.
Hi aamirqhanchi,
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

IN(@ListVariable) and TABLE Data Type

Hi this question has been asked several times and some solution has been
provided already. But the one I am facing is with a twist. I need to use the
IN() clause with a variable as its parameter. The variable is a list of comma
separated character values all enclosed in pairs of single quotes. I could
have solved this problem by enclosing the final query in a single quote and
running Exec command on it (with the Variable list outside the quotes) but I
also need to use a Table data type variable which raises error when EXEC
command is run.
Followig is the example that may explain well.
I have oversimplified this example and it does things that we would not do
in normal situation
use pubs;
-- declare and set Table variable
declare @.TableVariable TABLE ( col char(4) );
INSERT @.TableVariable
Select pub_id FROM publishers
;
--declare and set CSV single quoted characters list
declare @.ListVariable varchar(100);
set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
publishers table, promise
--the query where the Table variable is used as well as the IN() clause is
used
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN (@.ListVariable)
-- returns 0 rows
--if we use Exec by replacing the last code section above with as following
declare @.command varchar(2000)
set @.command='
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN ('+@.ListVariable+')
'
exec (@.command)
--Then we get the error message:
-- Must declare the variable '@.TableVariable'.On Mon, 7 Feb 2005 13:27:06 -0800, "aamirghanchi@.yahoo.com"
<aamirghanchi@.yahoo.com@.discussions.microsoft.com> wrote:
> I need to use the
>IN() clause with a variable as its parameter. The variable is a list of comma
>separated character values all enclosed in pairs of single quotes. I could
>have solved this problem by enclosing the final query in a single quote and
>running Exec command on it (with the Variable list outside the quotes) but I
>also need to use a Table data type variable which raises error when EXEC
>command is run.
Hi aamirqhanchi,
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

In SQL Server,I need to pull the records that have improper international phone number of format

Hi can you please help me to come up with a solution where i can pull records that have improper international telephone number format. like for example: These are set of records that have length 16 characters.

TelNumber.

091-3 4-43 -5678 ->here including numbers it even counts the spaces ,'-' between numbers and gives length as 16.

509--66-4 3-8887

670- 67--077-546.

908-898-654-3421 ->only 4th and 5th records are valid records that match the standard format we follow:

972-567-553-7689 ccc-aaa-nnn-nnnn where ccc =country code;aaa =area code;nnn-nnnn =phone number

I need a query that can pull first 3 record types...

thanks,

Comalkatar

I think that this will work for you:

Code Snippet

DECLARE @.Phones table
( TelNumber varchar(20) )

SET NOCOUNT ON

INSERT INTO @.Phones VALUES ( '091-3 4-43 -5678' )
INSERT INTO @.Phones VALUES ( '509--66-4 3-8887' )
INSERT INTO @.Phones VALUES ( '670- 67--077-546.' )
INSERT INTO @.Phones VALUES ( '908-898-654-3421' )
INSERT INTO @.Phones VALUES ( '972-567-553-7689' )

SELECT TelNumber
FROM @.Phones
WHERE TelNumber NOT LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'

TelNumber
--
091-3 4-43 -5678
509--66-4 3-8887
670- 67--077-546.

|||

Here You go...(using Regular Expression)

Code Snippet

Create Table #telephones (

TelNumber Varchar(100)

);

Insert Into #telephones Values('091-3 4-43 -5678');

Insert Into #telephones Values('509--66-4 3-8887');

Insert Into #telephones Values('70- 67--077-546.');

Insert Into #telephones Values('908-898-654-3421');

Insert Into #telephones Values('972-567-553-7689');

Select

*

From

#telephones

Where

TelNumber NOT Like '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'