Conditional Where Clause With Comma Delimited String And Link Table

Jul 24, 2007

 I have 3 tables:

tblUsers
userID int PK
(...)

tblSports
SportID int PK
(...)


tblUsersAndSports  (contains the link between users and sports..a single user may have multiple entries in this table)
Usercode int
SportID int


Now I want a stored proc that enables visitors to search on all user that have a specific sportID.
The SportIDs to search on are in the var @sports as a comma delimited string,like '3,6,7'
@sports may also be null (or an empty string if that is more convenient for building the SQL) when a visitor does not want to search on any of the sports a user practices, in that case no selection based on the sport criteria should be done, so ONLY filter on sports when the value of @sports is not null

pseudo code:

select * from tblUsers
where
   if @sports not null
    user.sports in @sports

and username=@username
and age=@age


help is greatly appreciated!

View 10 Replies


ADVERTISEMENT

How Do I Split And Insert A Comma Delimited String To A Table?

Oct 13, 2004

I am passing in a string of dates, delimited by a comma.

So

01/01/04, 02/01/04, 03/01/04

etc

I would like to enter each of these values into a table via an INSERT stored procedure.

Does anyone have any code for this?

View 1 Replies View Related

SQL Server 2008 :: Search Each And Every String In Comma Delimited String Input (AND Condition)

Mar 10, 2015

I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.

Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'

SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'

As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.

View 3 Replies View Related

Quotes Around The Comma Delimited String

Oct 18, 2005

I am trying to build a dynamic where  statement for my sql stored prcoedure
  if len(@Office) > 0   select @strWhereClause = N'cvEh.chOfficeId in (select id from dbo.csfParseDeLimitedText( ' + @vchOffice  +  ',' + ''',''' + '))' + ' and ' + @strWhereClause
In this case users can enter comma delimited string in their search criteria. So if they enter we1, we2, we3 then my sql statement should look like
select @strWhereClause = cvEh.chOfficeId in (select id from dbo.csfParseDeLimitedText('we1', 'we2', 'we3'),',')
My csfParseDeLimitedText function looks like this
Create  FUNCTION [dbo].[csfParseDeLimitedText] (@p_text varchar(4000), @p_Delimeter char(1))RETURNS @results TABLE (id varchar(100))ASBEGIN declare @i1 varchar(200)declare @i2 varchar(200)declare @tempResults Table (id varchar(100))while len(@p_text) > 0 and charindex(@p_Delimeter, @p_text) <> 0beginselect @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1)insert @tempResults select @i1select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text))endinsert @tempResults select @p_text
insert @resultsselect *from @tempResultsreturnEND
 
My problem is it does not put quotes around the comma delimited stringso I want to put 'we1' , 'we2'. These single quotes are not coming in the dynamic sql statement. How can I modify my query so that single quotes around each entry should show up.
Any help will be greatky appreciated.
Thanks

View 1 Replies View Related

SELECT WHERE IN Comma Delimited String

Jun 3, 2008

Hello

I have a table with column Options where each field contains a comma delimited list of ID numbers.

I want to select any records where a certain ID number appears in that list.

So something like:

SELECT * FROM Table t1
WHERE MyID IN (SELECT Options FROM Table t2 WHERE t1.ID = t2.ID)

But that gives me the error:

Syntax error converting the varchar value '39,20' to a column of data type int.

I feel I'm close though! Could anyone point me in the right direction?

Many thanks

Square

View 3 Replies View Related

Insert A Comma Delimited String

Nov 22, 2013

I have a problem in a stored procedure trying to insert a comma delimited list into rows.The parameters for example would be somthing like....

@GarageID int = 20,
@Cars nVarChar(200) = Ford~Fiesta,BMW~320,Volvo~340,Jaguar~XJS

I need to split where the comma is for each new row, and separate each value from either side of the '~' like so....

GarageID Make Model
--------------------------------
20 Ford Fiesta
20 BMW 320
20 Volvo 340
20 Jaguar XJS

View 2 Replies View Related

Splitting A Comma Delimited String

Oct 1, 2007

I have a string like say: '3:4:5:4,2:4:1,4:1:2:5:2'. Now I need to split the substrings delimited by commas. So my final output shall be

3:4:5:4
2:4:1
4:1:2:5:2


I did write a piece of code to achieve the same, but I feel its not so efficient. Can anyone suggest me a better way, if any? My code is as follows:

Declare @person as varchar(255), @cnt smallint,@loc smallint,@prevloc smallint, @str varchar(255)
Select @prevloc=0,@loc=1,@cnt=1,@person = '3:4:5:4,2:4:1,4:1:2:5:2'
While @loc != 0
begin
set @prevloc=(case when @loc = 1 then 0 else @loc end) +1
set @loc = charindex(',',@person,@loc+1)
Set @str = substring(@person,@prevloc,(Case when @loc = 0 then len(@person) - @prevloc + 1 else @loc - @prevloc end))
print 'String = ' + @Str
set @cnt=@cnt+1
end

RESULT
------
String = 3:4:5:4
String = 2:4:1
String = 4:1:2:5:2


Note: My actual purpose is to also sub split it again with ':' delimiter too. So looking for an efficient code.

View 4 Replies View Related

Stored Procedure And Comma Delimited String

Dec 8, 2006

I'm passing a comma delimited string to my SP, e.g.:"3,8,10,16,23,24"I need to retreive each number in this string and for every number found I need to execute some sode, say add "AND SportID="+numberfoundHow can I do that?

View 6 Replies View Related

Multiple Rows Into A Comma Delimited String

May 22, 2007

I have the following table:id name1 yes2 no3 what4 is5 this6 niceThe amount of rows can vary from 1 to 50. I only need the name column.What SQL statement do I have to execute to get the following:yes,no,what,is,this,nice,  (trailing , is acceptable)Thanks!

View 1 Replies View Related

How To Parse A String Column With Comma Delimited

Jul 20, 2005

Hi,I would like to parse out each value that is seperatedby a comma in a field and use that value to join to another table.What would be the easiest way to do so without having towrite a function or routine ?EX.Table AAACOL1 COL21 11, 124, 1562 11, 505, 600, 700, ...Table BBBCOL1 COL211 Desc11124 Desc124156 Desc 156

View 2 Replies View Related

Transact SQL :: How To Split Comma Delimited String

Oct 8, 2008

I have a parameter called Id in my SP which will be of nvarchar data type and i'm going to get the multiple ids at a time seperated by commas in that parameter from the application. Now my requirement is to update a table with these comma seperated ids in seperate rows.

For example, if i have 2 parameters called Id1 and Id2. Id1 will contain only one value and will be of int data type and Id2 will be of nvarchar data type as i can get multiple ids delimited by a comma from the application.

Suppose Id1 = 1 and Id2 = '1,2,3,4'. Then I have to update id2 in the tables seperately like wherever Id1 is '1' i need to update Id2 column for 4 rows with the value 1, 2, 3, 4 respectively in different rows.
 
how can i do this in T-SQL? How can i split the data of parameter Id2 in 4 different rows?

View 24 Replies View Related

Passing A Comma Delimited String Of Parameters To A Stored Proc

Jul 30, 2007

Hello,
I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.

I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.
But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?

Thanks.

View 3 Replies View Related

String Parameter With Comma To Where Clause

Mar 20, 2014

I want to bring a string parameter to where clause like this:

select .. from ...
Where Code IN (@Code)

When @Code is 'ABC' it works but when @code is 'ABC, XYZ' it won't work. I know for IN statement we should use IN ('ABC', 'XYZ') and I tried make @code = '''ABC'', ''XYZ''' still not working. I tried

Print '''ABC'', ''XYZ'''

I got 'ABC', 'XYZ' but why it does not work in @Code?

View 4 Replies View Related

Split Function To Separate Comma Delimited Values Into A Table

Oct 21, 2012

I have a stored procedure that is passed two values. Both are strings representations of GUID values, the first is a single value, the second is a comma delimited string of values.In the stored procedure I call a split function to separate the comma delimited values into a table and this is used in my WHERE clause to filter my select results.This is an example:

Code:
SELECT
item.uiGUID as ItemGUID,
stores.strStoreName as Store,
location.strLocationName as Location
FROM tblItems as item
INNER JOIN tblStoreLocations as location
ON item.uiLocationGUID = location.uiGUID
INNER JOIN tblStores as stores
ON location.uiStoreGUID = stores.uiGUID
WHERE CAST(item.uiGUID as varchar(36)) IN (SELECT Value FROM dbo.Split(',',@ItemGUIDList))

When I run this query in the management studio, passing a list of 5 values in the second parameter, my results include one item for each of the 5 values. However, when I pass the parameters from my ASP project, (I've verified the values I think are being passed are indeed being passed), I only get one item.I believe the error is in my split function. Both split functions return the same results in the SQL management studio, but only one returns the correct results in the the ASP project.

When I use this version of the function it returns the correct table values to the calling application, but it chokes when the item list does not have a trailing comma. I figure that to be a bug in the SQL function.

Code:
CREATE FUNCTION [dbo].[Split]
( @Delimiter varchar(5),
@List varchar(8000)
)
RETURNS @TableOfValues table
( RowID smallint IDENTITY(1,1),
[Value] varchar(50)

[code]....

View 7 Replies View Related

Comma Delimited File With Comma Inside A Column

Nov 9, 2007

Using Flat File Connection Manager, I am specifying Text Qualifier = Double quotes{"}, and i have TXT file with one column for lastname and first name as "LN,FN", and settings are set to comma delimted, now the connectin manager is creating two different columns for LN and FN,

it was never a problem in DTS 2000.

any work around.

Thanks,

View 7 Replies View Related

Transact SQL :: Function To Return Comma Delimited Values From Table Columns

May 12, 2015

I've to write a function to return a comma delimited values from a table columns

If a table has Tab1 ( Col1,Col2,Col3).

E.g. as below ( the columnName content I want to use as columns for my pivot table

CREATE FUNCTION [RPT].[GetListOfCol]
(
@vCat NVARCHAR(4000)
)
RETURNS @PList
AS
BEGIN
SELECT @PList += N', [' + [ColumnName] +']'
FROM [ETL].[TableDef]
WHERE [IsActive] = 1
AND [Category] = @vCat
RETURN;
END;

I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....

View 13 Replies View Related

Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.

Feb 13, 2006

We have the following two tables :

Link  ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )

The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.

The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).

We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.

For instance,

Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.

Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.

View 1 Replies View Related

SELECT WHERE (any Value In Comma Delimited List) IN (comma Delimited List)

Jul 2, 2005

I want to allow visitors to filter a list of events to show only those belonging to categories selected from a checklist.

Here is an approach I am trying:

TABLE Events(EventID int, Categories varchar(200))

EventID Catetories
--------------------------
1           ‘6,8,9’
2           ‘2,3’

PROCEDURE ListFilteredEvents
   @FilterList varchar(200)    -- contains ‘3,5’
AS
SELECT EventID FROM Events
WHERE (any value in Categories) IN @FilterList

Result:

EventID
----------
2

How can I select all records where any value in the Categories column
matches a value in @FilterList. In this example, record 2 would be
selected since it belongs to category 3, which is also in @FilterList.

I’ve looked at the table of numbers approach, which works when
selecting records where a column value is in the parameter list, but I
can’t see how to make this work when the column itself also contains a
comma delimited list.

Can someone suggest an approach?

Any examples would be greatly appreciated!
Gary

View 3 Replies View Related

Stored Procedure To Parse Delimited String To Table?

Apr 10, 2015

SP to parse a delimited string and insert the result in a table. I am using SQL Server 2008 R2. I have 2 tables - RawData & WIP. I have Robots on a manufacturing line capable of moving data to a DB. I move the raw data to RawData. On insert [RawData], I want to parse the string and move the contents to WIP as indicated below. I will run reports against the WIP Table.

Also, after the string is parsed, I'd like to change the Archive column, the _0 at the end of the raw string to 1 in the WIP table to indicate a successful parse.

Sample Strings - [RawData Table]
04102015_114830_10_013_9_8_6_99999_Test 1_1_0
04102015_115030_10_013_9_8_6_99999_Test 2_1_0

Desired Output - [WIP Table]

Date Time Plant Program Line Zone Station BadgeID Message Alarm Archive
-----------------------------------------------------------------------------------
04102015 114830 10 13 9 8 6 99999 Test 1 1 1
04102015 115030 10 13 9 8 6 99999 Test 2 1 1

View 16 Replies View Related

Comma Delimited Row

Jun 23, 2006

Hi,

Good evening! I have a problem. I have a namelist coming from a distribution list of an active directory. When I converted it to csv file, the members reside in just one column and separated by a comma. I want the names to be separated in a row one by one. I tried it on excel and I used the transpose column but to no avail. My last resort is to import it on sql but the names on the column was cut and not complete. Do you have any idea how to do this. Your help is highly appreciated.

this is the sample file..

names
kelly.yap, lizzy.fox, yahoo, finance.dep, hope.miller, porly.john

the maximum names in a row is 566.

thanks in advance.
myBU

View 2 Replies View Related

Comma Delimited Results Help Please

Aug 11, 2004

Would like to have a view created to display the result at the bottom of this message. We will be using Dreamweaver to display the information from this view. Also, for the record, we are using sql 2000. Any help would be greatly appreciated.

tblservers
servid servername
1 server1
2 server2
3 server3

tblapplications
appid appname
1 app1
2 app2
3 app3

tblapplink
id appid servid
1 1 1
2 1 2
3 1 3
4 2 1
5 2 3
6 3 1

we want to display this information as below:

appname serverlist
app1 server1, server2, server3
app2 server1, server3
app3 server1

Thank you very much,
mrtwo

View 2 Replies View Related

Comma Delimited Processing

Dec 28, 2006

Suppose I have a table named Test (referred in the query below)

Category Indicators
ctgy1Y,,,,
ctgy2Y,Y,Y,N,
ctgy3,Y,,Y,

and If I would like to transform this table to

Category Indicators
ctgy1Y
ctgy2Y
ctgy2Y
ctgy2Y
ctgy3Y
ctgy3Y

I am able to do it using the logic below

CREATE TABLE dbo.Numbers (Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED)
WHILE COALESCE(SCOPE_IDENTITY(), 0) < 5
BEGIN
INSERT dbo.Numbers DEFAULT VALUES
END

SELECT category,
SUBSTRING( Value, Number, CHARINDEX( ',', Value + ',', Number ) - Number ) as program
FROM Test
inner
JOIN Numbers
ON SUBSTRING( ',' + Value, Number, 1 ) = ','
and CHARINDEX( ',', Value+',', Number ) - Number <> 0
where Number <= Len(Value) + 1

But I would like to Transform this table into something like the one below (where if 'Y' before 1st comma then Q1, if 'Y' Before 2nd comma then Q2 and so on

Category Indicators
ctgy1Q1
ctgy2Q1
ctgy2Q2
ctgy2Q3
ctgy3Q2
ctgy3Q4

What is the best and efficient way to obtain this? Any help will be greatly helpful.


Thanks
Ram

View 4 Replies View Related

Comma Delimited Errors

Apr 2, 2007

I need to import comma delimited text files into sql tables. in one of the column, there is a comma in the string itself. e.g.



Cust_ID Name Phone Address1 Address2





Date that I have:

001,juia, anderson,4694568855,,Cedar Spring



The data does not have double quote as text qualifiers. but as you see, on the Name column, there is a comma, which is not a delimiter. can anybody give any suggestions on how i can deal with that? i would appreciate it so much.



thanks

View 2 Replies View Related

Comma Delimited List Of Views

Aug 13, 2007

Is it possible to get a comma delimited list of the views in a DB?

View 7 Replies View Related

How Can I Get A Comma Delimited List Of The Views In My Db

Mar 18, 2008

How can I get a comma delimited list of the views in my db?
("view1","view2","view3",etc...)

View 3 Replies View Related

Help - How To Select From A Comma Delimited Field ?

Nov 2, 2003

Dear SQL,

Lets say I have a field: User_Names

and it can have more than one name
example: "Yovav,John,Shon"

How can I select all the records that has "Yovav" in them ?

I try some like this:
SELECT * FROM User_Table WHERE User_Names IN ('Yovav')

but it only works if the field User_Names only has "Yovav" in it with no extras...


is there some SQL function to tell if string is found in a field ?

Any hope 4 me ?

View 2 Replies View Related

Comma Delimited Text File

May 18, 2004

Hi I'm pretty new to using Microsoft Visual C# .NET and I want to upload a comma delimited text file from my local machine into a table in an sql server database through a web app. How would I go about programming this and what controls do I need? Any help would be much appreciated. Thanks in advance.

View 4 Replies View Related

Passing Comma Delimited Parameter To SP

Apr 14, 2005

Is this possible? I find it hard to believe that this could be sooo difficult. I have a simple select stored procedure that has one parameter. My application is passing a comma delimited string of values to be used in the IN clause.
Ex: Where x In(@parametername)
the x column is an integer. How can one work around this???
Thanks!

View 4 Replies View Related

Creating A Comma Delimited List

Jul 11, 2005

Hi,

I have a complex query where each row in the final dataset is a
product.
However each product has a number of authors associated with it. 
What I
would like to do is have a query/subroutine join the authors to the
product,
as a string:


ProductID  
Title                       
Authors
1           The Sacred and the Profane   John Rieggle, George
Alexi
2           Dancing
in the Dark          Dan
Brown, Peter Kay, Paul
Dwebinski

Products
Table
==============
ProductID
Title

Authors
Table
=============
AuthorID
Name

Product Authors
Table
=====================
AuthorID
ProductID

Is this at all
possible?

Thanks

jr.

View 3 Replies View Related

Split Up Comma-delimited Field

Jun 7, 2002

I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.

Any ideas how I could do this in T-SQL?

View 9 Replies View Related

Inner Join On A Comma Delimited List?

Jul 12, 2006

Hi,

I have the following query:


SELECT *
FROM News INNER JOIN
Newsletters ON News.ID = Newsletters.fkNewsID


My problem is that fkNewsID can contain a comma delimited list of various IDs. Is there a way to properly do the join in this case?

View 1 Replies View Related

Help Moving Comma Delimited Data

Jan 21, 2004

I posted this originally in the incorrect forum I believe so I am reposting this in here which I believe is the proper place.

I need to move essentially a flat file from one server to another one and export it into a database on the second server. Does anyone have an easy process to accomplish this? I am currently at a loss. Any advice would be much appreciated.

Thanks

View 14 Replies View Related

Export To Comma Delimited File

Aug 4, 2007

I'm trying to upload a small Web application with a one table database. The hosting company, GoDaddy requires that I upload the database as a comma delimited file.
I created the database in Visual Web Developer Express but also have Visual Studio and SQL Server Management Studio Express.
I can't figure out how to export the database into a comma delimited file using any of these tools.
This should be simple like it is in Access but that doesn't seem to be the case. This is holding up deploying my Web Application.

Can anyone help me?

Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved