SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Not in csv string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 06/06/2014 :  05:20:45  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 06/06/2014 :  11:44:43  Show Profile  Reply with Quote
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+',%'
Go to Top of Page

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 06/09/2014 :  10:23:04  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 06/09/2014 :  10:25:14  Show Profile  Reply with Quote
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.
Go to Top of Page

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 06/09/2014 :  11:38:13  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
if i pass fr and in the excluding field it has de,pt,fr,nl
I do not want it to return any field
Go to Top of Page

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 06/09/2014 :  12:12:51  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 06/09/2014 :  15:39:23  Show Profile  Reply with Quote
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


Go to Top of Page

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 06/10/2014 :  05:03:05  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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+',%'
Go to Top of Page

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 06/10/2014 :  05:35:41  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Solved it with charindex

Thanks anyway for the effort

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000