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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
Jul 24, 2007
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!
View 10 Replies
View Related
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
Jun 28, 2004
if i send the string
2,3,4,5 to a stored procedure...
is there a way i could split those values for input into the database? no, right? i would need a seperate stored procedure that would take each value one at a time...correct?
View 7 Replies
View Related
Apr 24, 2015
Our front end saves all IP addresses used by a customer as a comma separated string, we need to analyse these to check for blocked IPs which are all stored in another table.
A LIKE statement comparing each string with the 100 or so excluded IPs will be very expensive so I'm thinking it would be less so to split out the comma separated values into tables.
The problem we have is that we never know how many IPs could be stored against a customer, so I'm guessing a function would be the way forward but this is the point I get stuck.
I can remove the 1st IP address into a new column and produce the new list ready for the next removal, also as part of this we would need to create new columns on the fly depending on how many IPs are in the column.
This needs to be repeated for each row
SELECT IP_List
, LEFT(IP_List, CHARINDEX(',', IP_List) - 1) AS IP_1
, REPLACE(IP_List, LEFT(IP_List, CHARINDEX(',', IP_List) +0), '') AS NewIPList1
FROM IpExclusionTest
Results:
IP_List
109.224.216.4,146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119
IP_1
109.224.216.4
NewIPList1
146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119
View 8 Replies
View Related
May 6, 2015
Interest rate has been stored in comments column along with other information ( e.g. mike's student loan is 5% and car payment is $ 150). I need to extract 5% using Contains .. Why Contains? because it's a 1.7 m rows dataset and searching for fours specific interest rate values (e.g. 3%, 9%, 12% and 15%)
View 3 Replies
View Related
Jun 20, 2015
DECLARE @search VARCHAR(10);
SET @search = 'dog,COW';
WITH T(C)
AS
(
SELECT 'Cat,Dog,Sparrow,Trout,Cow,Seahorse'
UNION ALL
SELECT 'Cat,Dog,Sparrow,Trout,Cow,Seahorse'
)
SELECT *
FROM T
WHERE C LIKE '%' + @search + '%'
I have a string in a cell 'Cat,Dog,Sparrow,Trout,Cow,Seahorse'
I want to search any of the two names from the string.
Search string:'cat,cow'
Result:no result (not working),[size="7"][/size]
Search string:'cat,dog'
result :given string(working fine )
View 2 Replies
View Related
Oct 6, 2015
I have a table like this :
ID Description ParentID Level
B.01 Parent 1 H
B.01.01 Parent 1.1 B.01 H
B.01.01.01 Parent 1.1.1 B.01.01 H
B.01.01.01.01 Detail 1 B.01.01.01 D
B.01.01.01.02 Detail 2 B.01.01.01 D
[Code] .....
That means, only select Level=H, and display the last record of H with concatenated the description of each rows delimited with comma.
View 2 Replies
View Related
Sep 3, 2015
I am struggling on using bcp to import data. Here is my steps:
1. I created a Test database on my localhost
2. In the Test database, I created a Test table, the query is here for your convenience:
CREATE TABLE [dbo].[Test](
[id] [int] IDENTITY(1,1) NOT NULL,
[network_group_name] [varchar](128) NULL,
[IP] [varchar](15) NULL,
[OS] [varchar](128) NULL,
[Code] ....
I then create the format file used in bcp:
bcp Test.dbo.Test format nul -c -t, -f C:RXieSQLTest.fmt –T
Here is the format file:
9.0
8
1 SQLCHAR 0 12 "," 1 id ""
2 SQLCHAR 0 128 "," 2 network_group_name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 15 "," 3 IP SQL_Latin1_General_CP1_CI_AS
[Code] ...
The data file is called 20150902FullTest.rpt and the first couple lines (first line is the header and followed by two rows) are posted here:
network_group_name,IP,OS,App_Name,vuln_name,host_score,recordswritten
Domestic,10.216.56.88,Windows XP SP3,Adobe / Macromedia Flash Player,APSB14-17: Adobe Flash Player CVE-2014-0537 Vulnerability,4350,2015-09-01 09:55:07.720
Domestic,10.216.56.88,Windows XP SP3,Adobe / Macromedia Flash Player,APSB14-17: Adobe Flash Player CVE-2014-0539 Vulnerability,4350,2015-09-01 09:55:07.720
With the format file and the data file, I use the following bcp command:
bcp Test.dbo.Test in C:RxieSQL20150902FullTest.rpt -f C:RxieSQLTest.fmt -T
I got the following error messages:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
[Code] ...
I do want to mention here is the rpt file contains three BOM characters EF BB BF at the beginning of the file.
View 9 Replies
View Related
Nov 30, 2005
Hi, I need to search a column in my database (varchar:50) that contains a comma delimited string of numbers (i.e. 1, 2, 3, 4, 5, 10). Currently, I am doing the following SQL query:
SELECT * FROM people WHERE clubs_belongs_to LIKE '%1%'
Where 1 is the number I'm searching for. The problem with the query above is that it returns records that contain 1, 10, 11, 12, 13, etc. in the clubs_belongs_to field. I want the query to only return those people who belong to club number 1, not 10, 11, 12, etc. Please help. Thanks in advance. I have tried using IN instead of LIKE, but that didn't seem to get the results I wanted either.
View 4 Replies
View Related
Jul 13, 2006
I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam
View 2 Replies
View Related
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
Sep 1, 2015
Is there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
View 3 Replies
View Related
Apr 7, 2015
I have a need to create a delimited string so that I can use this to create a data driven subscription on SSRS.
In the below code, I need to create a delimited string using the branch number, grouped by the email address
USE tempdb
GO
IF OBJECT_ID('tempdb..#emails') IS NOT NULL
BEGIN
DROP TABLE #emails
[code]....
View 7 Replies
View Related
Oct 19, 2015
I am having a field 'Flight Route that holds hyphen delimited character sequences.
E.g. : ABC-BCD-DEF-EFG.
My requirement is like this:
If the flight route is:
ABC-BCD-BCD-DEF make it ABC-BCD-DEF
ABC-ABC-BCD-DEF make it ABC-BCD-DEF
i.e. 'whenever a sequence repeats,only one appearance of that sequence should be displayed.The field Flight Route has to be updated with this replaced string.
View 6 Replies
View Related
Jul 3, 2013
parsing any delimited string (in above example it is using ',' as parsing delimiter. This query can be useful in many business scenarios where in we have input data as a long string containing delimited values.
declare
v_sql VARCHAR2(2000);
v_pos INTEGER;
v_differentiator VARCHAR2(10);
[code]...
View 4 Replies
View Related
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
May 20, 2008
So we have a field called forenames, and it needs to be split into fields forename_1, forename_2, forename_3, forename_4 (don't ask).
Ok, I've come up with this so far, which works, but is pretty nacky in my opinion. Has any one got a better way of achieving this?
SELECT forenames
, Replace(forenames, ' ', '.')
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 1)) As [f1]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 2)) As [f2]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 3)) As [f3]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 4)) As [f4]
FROM ( SELECT 'John' As [forenames]
UNION SELECT 'John Paul'
UNION SELECT 'John Paul George'
UNION SELECT 'John Paul George Ringo'
) As [x]
Results
forenames (no column name) f1 f2 f3 f4
---------------------- ---------------------- ---- ---- ------ -----
John John John NULLNULL NULL
John Paul John.Paul John PaulNULL NULL
John Paul George John.Paul.George John PaulGeorgeNULL
John Paul George Ringo John.Paul.George.Ringo John PaulGeorgeRingo
View 7 Replies
View Related
Jan 27, 2008
I have a checkbox list on datalist as one column. when user selects more than one checkbox and click on apply. i concatenate IDs of checkboxes as '1,2'3' for e.g. and sending that to Stroe Procedure as varchar datatype parametrer. In Procedure i wanna update status of all three selected and i am using statement "update tbl set status=1 where pageid in('1,2,3'). It is saying it cannot convert varchar to int.
How can i do this task?
Thanks in advance.
View 2 Replies
View Related
Mar 4, 2007
It's probably a most basic operation but I can't find how to enable this. Using SQL Server 2005
eg: how would I get this sort of step to work?
INSERT INTO company VALUES (10001,"Apps'r'Us");
where the schema is
(companyID int,
companyName varchar(50))
View 1 Replies
View Related
Mar 11, 2008
I have a string
@string = 'abc,def,ghi'
Now I have to display
abc
def
ghi
I have to separate a comma in that
Is there an function like split in sql
plz help me
Thanks in Advance
Suresh Kumar
View 5 Replies
View Related
Mar 11, 2008
Hi All,
I have one field with different values like
F1
A,B,C,
D,E
G,H,I,
My requirement is to remove the last comma from that string. I need the output should look like
F1
A,B,C
D,E
G,H,I
thanks,
Mears
View 9 Replies
View Related
Mar 6, 2014
I have a single string "XYZ00001|Test_b|XYZ00002|Test_a|XYZ00003|Test_c" that will continue to grow over time.
Is there a way I can extract the values from the string into two separate columns?
XYZ0001 Test_b
XYZ0002 Test_a
XYZ0003 Test_c
View 5 Replies
View Related
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
