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.
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
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?
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!
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
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?
I have 3 tables:tblUsersuserID int PK(...)tblSportsSportID int PK(...)tblUsersAndSports (contains the link between users and sports..a single user may have multiple entries in this table)Usercode intSportID intNow 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 nullpseudo code:select * from tblUserswhere if @sports not null user.sports in @sportsand username=@usernameand age=@agehelp is greatly appreciated!
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?
I am having a problem using the Bulk Insert task. I am getting the msg: SSIS package "Package.dtsx" starting. Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "You do not have permission to use the bulk load statement.". Task failed: Bulk Insert Task SSIS package "Package.dtsx" finished: Success.
I have been granted ownership of the database. I also tried in one of my old databases that I just finished developing and I got the same msg.
The file I am importing is comma delimited. I am importing it into a table that has 50 bytes allocation for each field (the max input field size is 40 bytes).
The connection is solid; Format = “Specify� RowDelimiter = {CR}{LF} columnDelimiter = Comma {,}
No other options are set.
The data looks like: "tstLName","tstFname","000 N Tst DR","IDAHO sp","ID","00000000",
Hi, all:I have a form which lets users choose more than one value for each question.But how do I insert each value as a separate row in my table (instead ofhaving the values submitted as a comma-delimited list)?Thanks for your help.J
I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc: exec proc_Add_Product_Files @FileName = N'This is just a test.doc', @FileDescription = N'test', @FileSize = 24064, @LanguageID = NULL, @DocumentCategoryID = 1, @ComplianceID = NULL, @SubmittedBy = N'Kevin McPhail', @SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @ProductID = N'10,11,8' Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs) CREATE PROCEDURE proc_Add_Product_Files_v2/*Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a rowinto productfiles for each product that the file being uploaded pertains to. */@FileName varchar(150),@FileDescription varchar(150),@FileSize int,@LanguageID int,@DocumentCategoryID int,@ComplianceID int,@SubmittedBy varchar(50),@SubmittedDate datetime,@ProductID varchar(150) ASBEGIN DECLARE @FileID INT SET NOCOUNT ON /*Insert into the files table and retrieve the primary key of the new record using @@identity*/ INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate) Values (@FileName, @FileDescription, @FileSize, @LanguageID, @DocumentCategoryID, @ComplianceID, @SubmittedBy, @SubmittedDate) Select @FileID=@@Identity /*Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.*/ DECLARE @ProductFilesInsert varchar(2000) SET @ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @ProductID + ')' exec(@ProductFilesInsert) EndGO
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,
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.
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.
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.
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
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.
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.
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!
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
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.
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.
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.
I need to split a string, based on comma delimetrs, into columns of a table.
I am using SQL Server 2005. The plan is to use webmethods (Integration Software) to receive a flat file, loop through the records in that flat file and during each iteration of the loop call a SQL stored procedure that will split the record, based on comma delimetrs, into columns of a table.
A typical record will look like this: AP05-07,ACTUAL,ZA01,......
I have looked at some of the past solutions to this type of post, but am battling to understand....
So if its possible, is there a simple way to create a stored procedure that will do this?
