I am trying to return as a dataset of this that shows the ID as column 1 then each value in the 2nd column as individual columns if I use SELECT LEFT('2ndColumn,PATINDEX(',',2ndColum)-1) I can return the first value as a column but then can't return any further values individually after the first column, I am just learning the new functionality in SSIS so not sure whether this would be my answer as apposed to T-SQL, if anyone has any advice on this it would be greatly appreciated?
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
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 need a help in SQL Server 2000. I am having a string variable in the format like -- (1,23,445,5,12) I need to take single value at a time (like 1 for 1st, 23 for 2nd and so on) from the variable and update the database accordingly. This is like a FOR loop. Can anyone help me out in splitting the variable using the comma separator...
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?
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.
I have a table with millions of rows, 1 particular attribute "FromPerson" contains a string of email addresses, names, etc... for example (formatting done by this window... not exists in database...completely cleaned to semi-colon delimited)
tomh@gmail.com; Snyder, John N.; jsnyder@yahoo.com; Miller, Jim; millerj@gmail.com; Tenbrow, Jack; Katie Winslow (can be x number of names, emails...)
as you can see... some of the delimited values match up with an email address, others have just names, or emails... I have successfully split this into 1 row per value per say, but how do i get them to match up the values that do. I think the splitting is really not helping the cause... what i want is this...
tomg@hotmail.com tomg@hotmail.com Snyder, John N. jsnyder@yahoo.com Miller, Jim millerj@gmail.com Tenbrow, Jack Tenbrow, Jack Katie Winslow Katie Winslow
there could/can be other attributes, like datetime stamp, domain(if any)... etc... but I think I can add that later...
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.
have a field in a table that has combined lastname,firstname and middle name like
combs,albert mike woods-athere,jane alice
The last and first name are separated by a comma, and the middle name by a space I need in tsql to split them to last name,first name and middle name I used below
Hi i want to create a table as follows :if exists (select * from dbo.sysobjects where id =object_id(N'[Indexes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [Indexes]GOCreate table Indexes(indexname Varchar(100), index_DescriptionVarchar(100), index_keys Varchar(100))GOINSERT INTO Indexes EXEC sp_helpindex 'SDM_Fact_Order_Detail'GOThis will give me a table (northwind)IX_Auto_SDM_Fact_FK_Shipped_Date nonclustered located onSAMIS_SDM_Index FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Dateclustered located onSAMIS_SDM_Data1FK_Insert_Date, FK_Insert_TimeAs you see sp_helpindex will give me a comma seperated field. I wantto split the third column FK_Insert_Date, FK_Insert_Time into a extrarowLike this :IX_Auto_SDM_Fact_FK_Shipped_Date FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_TimeCan anyone help me with this?ThanxHennie
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.
