more Arrays question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

more Arrays question

Author

Topic

kien
Starting Member

27 Posts

Posted - 2002-06-14 : 14:09:52

Hi everyone, I've read the previous posting on Arrays and the related links, and still can not solve my problem. Can someone please help me out here =)

I'm trying to acheive the following:
SELECT ... FROM...
WHERE Country LIKE %Canada% AND Country LIKE %Japan% AND etc...

The UNKNOWN number of countries is passed through the stored procedure in the format "Canada Japan US China ...etc"

The downloadable code from http://www.sqlteam.com/downloads/sp_parsearray.sql
helps, but i'm still having trouble on how to acheive the above WHERE statement.

Can someone "add some code" from the above link where it tells you to "-- Replace this select statement with your processing" or anywhere else that would make things work out.

Thanks a lot :D

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-14 : 14:12:17

Try this article:

http://www.sqlteam.com/item.asp?ItemID=5857

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-06-14 : 14:22:44

quote:
WHERE Country LIKE %Canada% AND Country LIKE %Japan% AND etc...

Read the article Rob posted, but a side-note to your question... You've mixed up your ANDs with your ORs. When you use AND in a WHERE clause, it becomes more restrictive, narrowing your search. When you use OR, it becomes less restrictive, widening your search to include more rows. When you are referencing the same field multiple times, you almost always want to use ORs. The only way your example would come back with any rows is if you had a row where the Country was 'Canada Japan' which makes no sense whatsoever. Instead, you'll have some rows with Canada and some rows with Japan, so your query would be WHERE Country = 'Canada' OR Country = 'Japan' which would return all rows with canada and all rows with Japan (see how the AND and OR can so easily get mixed up?)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-14 : 14:43:19

There is another solution if you are on SQL2K. You can use a table-valued user-defined function. I have adapted the sp_parsearray...


create function ufn_ParseArray
(
    @array     varchar(8000),
    @separator varchar(10)
) 
RETURNS @Parsed TABLE
(
    ArrayID    INT     IDENTITY(1,1),
    Value      VARCHAR(1000)
)
AS
BEGIN

DECLARE @separator_position INT
DECLARE @array_value        VARCHAR(1000) -- this holds each array value as it is returned


--Special case
IF DATALENGTH(ISNULL(@array,'')) = 0
BEGIN
    INSERT INTO @Parsed (Value) VALUES (NULL)
    RETURN
END


SET @array = @array + @separator

-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%' , @array) <> 0 
BEGIN

    SELECT @separator_position =  PATINDEX('%' + @separator + '%' , @array)
    SELECT @array_value = LEFT(@array, @separator_position - 1)
    
    INSERT INTO @Parsed (Value) VALUES (@array_value)

    SELECT @array = STUFF(@array, 1, @separator_position  + datalength(@separator)-1, '')
END

RETURN
END
GO

Once you have that you can join to it as part of your from clause ...


declare @search varchar(8000)
select @search = 'canada,usa,japan,mexico'
select
    <column_list>
from
    <sometable> a
    inner join dbo.ufn_parsearray(@search,',') b
        on a.country like '%' + b.value + '%'

Personally, I like it....

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources