Not in csv string - 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 2008 Forums

Transact-SQL (2008)

Not in csv string

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2014-06-06 : 05:20:45

Hi all,

Got a field (excludecountries) with ISO2 country codes
ex: de,pt,fr,nl

i'm send 1 country code into the SP
ex: fr

how can i do this

where not target.excludecountries='fr'

thanks a lot

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-06 : 11:44:43

quote:
Originally posted by mike13

Hi all,

Got a field (excludecountries) with ISO2 country codes
ex: de,pt,fr,nl

i'm send 1 country code into the SP
ex: fr

how can i do this

where not target.excludecountries='fr'

thanks a lot

I did not understand the question,but I am guessing that you want to call the stored procedure with a list of comma-separated country codes. You could try this:

where  ','+@yourParameter+',' NOT LIKE '%,'+target.excludecountries+',%'

mike13
Posting Yak Master

219 Posts

Posted - 2014-06-09 : 10:23:04

Hi,

Sorry that i'm not clear.
In the DB in have a field (excludecountries( which has the comma-separated country codes ex: de,pt,fr,nl

And s value to the SP i'm passing 1 country ex: fr

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-09 : 10:25:14

quote:
Originally posted by mike13

Hi,

Sorry that i'm not clear.
In the DB in have a field (excludecountries( which has the comma-separated country codes ex: de,pt,fr,nl

And s value to the SP i'm passing 1 country ex: fr

And what is the result you are expecting? When you pass the parameter fr, do you want to exclude all rows where the column excludecountries has the string fr as part of it, such as the example you have posted de,pt,fr,nl

If that is what you are trying to get, the code I posted earlier should work.

mike13
Posting Yak Master

219 Posts

Posted - 2014-06-09 : 11:38:13

if i pass fr and in the excluding field it has de,pt,fr,nl
I do not want it to return any field

mike13
Posting Yak Master

219 Posts

Posted - 2014-06-09 : 12:12:51

in vb.net the code would be:

Dim excludecountries as string="de,pt,fr,nl"

if not instr(excludecountries,"fr") then

' Do something

end if

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-09 : 15:39:23

From your description, what I understood is that you have a table which could have zero or more rows in it. One of the columns in the table is excludecountries - which may have values like "de,pt,fr,nl" in one row, "de,pt,nl" in another row and so on. And, in such a case, if you send in the parameter as fr, you want to get the row that does not have fr in it, but not any row that has fr in it. If that is the case, then the query I posted earlier would do that.

where  ','+@yourParameter+',' NOT LIKE '%,'+target.excludecountries+',%'

On the other hand, if you don't want to get anything at all back if any row has fr in the excludecountries column

IF NOT EXISTS (SELECT * FROM YourTable where  ','+@yourParameter+',' NOT LIKE '%,'+target.excludecountries+',%')
begin
  --- rest of your query here
end

mike13
Posting Yak Master

219 Posts

Posted - 2014-06-10 : 05:03:05

Hi James,

I must be doind something wrong.
here the data
excludecountries has ,fr,nl,

SP, still returns 1 and should be 0

SELECT count(*)
FROM T_popup_main INNER JOIN
T_popup_target ON T_popup_main.id = T_popup_target.popupid
--WHERE (',-,' NOT LIKE '%,'+T_popup_target.excludecountries+',%' )
where ',de,' NOT LIKE '%,'+T_popup_target.excludecountries+',%'

mike13
Posting Yak Master

219 Posts

Posted - 2014-06-10 : 05:35:41

Solved it with charindex

Thanks anyway for the effort

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources