Splitting Delimited Column
Feb 19, 2008
I have a question for all the t-sql gurus.
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...
any ideas?
View 1 Replies
ADVERTISEMENT
Feb 12, 2008
Hi
I wondered if anyone could help me. I have a table that holds an ID in the first column then a list of values split by commas in the 2nd column i.e.:
IDColumn: 1
2ndCoumn: 1stvalue, 2ndvalue, 3rdvalue, 4thvalue
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?
Thanks in advance
Caralyn
View 7 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
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
Jun 22, 2007
Hi Everyone,I've been given the painstaking project of splitting a single column into multiple columns and rows. I have a solution set up in which I will be posting further down the post but I want to see if there is a much more efficient solution to this.sample data:create table tbl_list(pk_int_itmid int(5) Primary Key,vchar_desk vchar(300));create table tbl_test1(fk_int_itmid int(5) references tbl_list(pk_int_itmid),vchar_itm varchar(60));insert into tbl_list values(1, 'this item');insert into tbl_list values(2, 'that item');insert into tbl_list values(3, 'those items');insert into tbl_test1 values(1, 'A, B - C, D, E - F, G, H - I');insert into tbl_test1 values(2, 'J, K - L, M, N - O');insert into tbl_test1 values(3, 'P, Q - R');into this table:create table tbl_output(fk_int_itmid int(5) references tbl_list(pk_int_itmid),vchar_itmA varchar(60),vchar_itmB varchar(60),vchar_itmC varchar(60));Output in comma delimited form:'1', 'A', 'B', 'C''1', 'D', 'E', 'F''1', 'G', 'H', 'I''2', 'J', 'K', 'L''2', 'M', 'N', 'O''3', 'P', 'Q', 'R'my current solution:create view vw_itm_a as select fk_int_itmid, substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmCfrom tbl_test1where charindex(',',vchar_itm) >1Gocreate view vw_itm_b as select fk_int_itmid, substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmCfrom vw_itm_awhere charindex(',',vchar_itmC) >1;Gocreate view vw_itm_c as select fk_int_itmid, substring(vchar_itmC, 0, charindex('-',vchar_itmC)) as vchar_itmA,substring(vchar_itmC, charindex('-',vchar_itmC)+1 , charindex(',',vchar_itmC)-charindex('-',vchar_itmC)) as vchar_itmB,substring(vchar_itmC, charindex(',',vchar_itmC)+1) as vchar_itmCfrom vw_itm_bwhere charindex(',',vchar_itmC) >1;Go;create view vw_itm_d asselect fk_int_itmid, vchar_itmA, vchar_itmB,substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmCfrom vw_itm_a ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmidGo;create view vw_itm_e asselect fk_int_itmid, vchar_itmA, vchar_itmB,substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmCfrom vw_itm_c ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmidGo;create view vw_itm asselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_awhere fk_int_itmid not in (select fk_int_itmid from vw_itm_b)unionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_dunionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_bwhere fk_int_itmid not in (select fk_int_itmid from vw_itm_c)unionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_eunionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_cGo;select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC into tbl_outputfrom vw_itmIs there a much more efficient manner of handling this column splitting?ThanksDC
View 1 Replies
View Related
Dec 4, 2007
Hello
I have a column which contains a phone number followed by a name.
How can I split off the name.
Example 0898 500 400 Joe Bloggs
Thank you.
View 3 Replies
View Related
Jul 11, 2012
Running SQL 2005
Current Query is as follows:
SELECT
vPerf.DateTime,
vPerf.SampleCount,
vPerf.AverageValue,
vPerf.MinValue,
[code]....
This query was working well because I used to only be interested in one counter that was returned in the column, which was 'Free Megabytes'...I now have additional data that shows up as 'Total Disk Space'...Ideally, the query would return the total disk space next to the free megabytes on the same row for the same disk drive. Here is a couple rows of sample output:
AverageValueInstanceNameObjectNameCounterName
44549 C: LogicalDiskFree Megabytes
44548 C: LogicalDiskFree Megabytes
69452 C: LogicalDiskTotal Disk Space
69452 C: LogicalDiskTotal Disk Space
This is the ideal format, the average value column goes away:
InstanceNameObjectNameFree MegabytesTotal Disk SpaceC: LogicalDisk44549 69452
View 1 Replies
View Related
Feb 28, 2011
I have a table like this:
name | purchase amount | purchase date |
Joe--------20.00--------------12/1/06
Joe--------10.00--------------12/1/06
John-------10.00--------------1/15/06
Sam--------15.00--------------5/10/06
John-------20.00--------------11/1/06
Joe--------5.00---------------8/19/07
Sam--------5.00---------------10/8/07
Sam--------10.00--------------4/22/08
Sam--------15.00--------------4/22/08
and what I need is a query that will show name and the sum of purchase amount for specific years. Here is what it would look like:
name | purchase 2006 | purchase 2007 | purchase 2008
Joe 30.00 5.00 0
John 30.00 0 0
Sam 15.00 5.00 25.00
View 7 Replies
View Related
Jun 10, 2014
I have to split a column using comma a delimiter into multiple columns. I am able to do it if i know how many column will be present in the final output. But in daily run, the columns may vary randomly.
how to split columns without hardcoding how many columns it ll come.
This is the code am using
Code:
WITH Split_Names (Fil_id,Name, xmlname)
AS
(
SELECT Fil_ID,
[Code].....
View 8 Replies
View Related
May 21, 2008
Hi,
I have a table that has multiple postal codes in one of the columns. Those have to be split up one per line and stored in another table. The zip codes are comma seperated. Is there a function that can do this...?
Example data in ZipCodeTable. (Name and ZipCode are 2 columns in a table)
NameZipCode
Area119930,19970,19971,19944
Area219934,19938,19901,19903,19904
Area319994,19838
output Table should be:
NameZipCode
Area119930
Area119970
Area119971
Area119944
Area219934
Area219938
Area219901
Area219903
Area219904
Area319994
Area319838
Any thoughts on this would be of much help !!..
Thanks
View 7 Replies
View Related
Apr 28, 2014
I have 1 column (memberassignment_1) that has the string in it - Port=A; Code=B; Sfx=C; Name=D; Night=E; Sig_Grp=F
I want to split this into 6 columns - Port, Code, sfx, Name, Night, Sig_Grp
EXAMPLE
PORT CODE SFX NAME NIGHT SIG_GRP
A B C D E F
View 11 Replies
View Related
Apr 30, 2008
Hi
I have a table which has data "dumped" into it nightly and i need to create a "clean" table. Below are some sample rows:
1|name1|john|1234
2|name2|fred|2378
DS|address1|address2|postcode|telephoneno|area|propertytype
all the columns are separated with a "|" but the amount of columns are not fixed, so in lines 1 & 2 they are 4 columns and in line 3 there is 7 columns
thanks in advanced
Rich
View 4 Replies
View Related
Nov 20, 2014
I need splitting one column data into number of columns.
Name. Below are the sample of names
Doxycycline 200 mg capsule
Effexor 100 mg tablet
Duragesic 50 mcg/hr Transderm Patch
Ambien CR 6.25 mg Tab
My desired results are below..
name dosemeasmisc
Doxcycline200mgcapsule
Effexor 100mgtablet
Duragesic 50mcgTransderm Patch
Ambien CR 6.25mgTab
View 1 Replies
View Related
Jan 18, 2006
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
View 1 Replies
View Related
Jun 22, 2015
Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.
For example
PostCode Latitude Longitude
66000/66100 42.696595 2.899370
20251/20270 42.196471 9.404951
Would become
PostCode Latitude Longitude
66000 42.696595 2.899370
66100 42.696595 2.899370
20251 42.196471 9.404951
20270 42.196471 9.404951
View 6 Replies
View Related
Jun 19, 2014
Trying to export a SQL Server 2012 table to CSV file, with tab delimited
Using the Export wizard it completes it no problem but behaving as expected,
Lets say I have 3 Columns
address1 NVARCHAR(50)
address2 NVARCHAR(50)
address3 NVARCHAR(50)
It is exporting it is doing
Column Width 50 (with value) |tab| Column Width 50 (with value) |tab| Column Width 50 (with value) |tab|
I was hoping for
Actual Value Length |tab| Actual Value Length |tab| Actual Value Length |tab|
View 2 Replies
View Related
Jan 13, 2015
I have a very interesting problem in T-SQL coding for which I can't figure out the solution. Actually there is a Line_1_Address column in our data warehouse address table which is being populated from various sources. Some sources have already concatenated house number + street address fields in the Line_1_Address column whereas one source has separated columns for both data fields.
Now I'm trying to extract data from this data warehouse table and I need to split the house number from street address and load it into separate columns in my destination table. In case there is no data for house number then I should load it as NULL.
The issue is that data in this Line_1_Address column is very inconsistent so I don't know which functions to use. Here is some sample data for your consideration:
Line_1_Address
101 E Commerce ST
120 E Commerce ST
2 Po Box
301 W. Bel Air Ave
West Main Street, PO Box 1388
[Code] .....
View 6 Replies
View Related
Mar 3, 2015
I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.
For Eg:
Description
APPEARANCE UNIT
BDV KV
DENSITY KG/L
View 9 Replies
View Related
Jun 22, 2015
Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.
For example:
PostCodeLatitudeLongitude
66000/6610042.6965952.899370
20251/2027042.1964719.404951
Would become
PostCodeLatitudeLongitude
6600042.6965952.899370
6610042.6965952.899370
2025142.1964719.404951
2027042.1964719.404951
how this can be done?
View 2 Replies
View Related
Dec 11, 2013
How do I write a query using the split function for the following requirement.I have a table in the following way
Identity Name Col1 Col2 Col3
1 Test1 1,2,3 200,300,400 3,4,6
2 Test2 3,4,5 300,455,600 2,3,8
I want an output in the following format
Identity Name Col1 Col2 Col3
1 Test1 1 200 3
1 Test1 2 300 4
1 Test1 3 400 6
2 Test2 3 300 2
2 Test2 4 455 3
2 Test2 5 600 8
If you see the data, first element in col1 is matched to first element in col2 and 3 after splitting the string.
View 2 Replies
View Related
Nov 14, 2014
I have a scenario wherein one of the column values in a row contains a string value which is non-delimited (as shown below). I need to split them by 2 characters and generate as many rows as count of set of 2 digits in that string.
Current record
RecordID Name CountyList
1 ABC 00
2 CDE 01020304
3 XYZ NULL
Expected output:
RecordID Name CountyList
1 ABC 00
2 CDE 01
2 CDE 02
2 CDE 03
2 CDE 04
3 XYZ NULL
I already have a solution in place to run it thru cursor and then do a while loop on the CountyList column by taking 2 digit value using Substring function (keeping start position dyanamic and jumping 2 positions).
View 2 Replies
View Related
Jan 22, 2006
Hello,I'm not getting any response to this on the SQLDTS newsgroup, so Ithought that I would try here:I just ran into this problem and I can't find any other mention of itthrough Google. I have a text file that is comma-delimited. It alsouses double quotes as text identifiers. A new column has been added tothe file, but currently has no values. I would like to finish mydevelopment so that when it does finally get some values, they will beimported as well. The problem is, the last column does not show up inDTS.I can reproduce this problem easily enough... create a text file withthe following two lines in it:1,"test",2,"test2",Now, create a new DTS package and add a text file connection. Point itto the new file and go through the properties for the file. You willnotice that on the second screen where it displays the preview of thedata there are only two columns shown.This does not happen if there is no text qualifier or if at least onerow has the final column value filled. Is there any way around thisproblem?Thanks!-Tom.
View 2 Replies
View Related
Oct 26, 2006
I don't know if this is possible, but I haven't been able to find anyinformation.I have two tables, for example:Table 1 (two columns, id and foo)id foo--- -----1 foo_a2 foo_b3 foo_cTable 2 (two columns, t1_id, and bar)t1_id bar------ ----1 bar_a1 bar_b1 bar_c2 bar_d3 bar_e3 bar_fWhat I'm shooting for is returning the result of a subquery as atext-delimited column. In this example, using a comma as thedelimiter:Recordset Returned:foo bars----- -----foo_a bar_a,bar_b,bar_cfoo_b bar_dfoo_c bar_e,bar_fI know that it's usually pretty trivial within the code that isquerying the database, but I'm wondering if the database itself can dothis.Is this possible, and if so, can someone please point me to how it canbe done?
View 8 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
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
Jun 18, 2014
I have a stored procedure that returns a single row based on a parameter of employee ID. This particular procedure uses a CTE to traverse our org structure. One of the columns is returning a delimited string of Windows login values and due to the business rules, it can contain duplicate values. I need to have that column contain only unique values - no dupes.
For example, this one column could contain something like this:
domainuser1;domainuser2;domainuser2;domainuser 3;
The need is to convert to this:
domainuser1;domainuser2;domainuser3;
I know that's a tall order.
View 1 Replies
View Related
Jun 13, 2008
I'm working on a sales commission report that will show commissions for up to 5 sales reps for each invoice. The invoice detail table contains separate columns for the commission rates payable to each rep, but for some reason the sale srep IDs are combined into one column. The salesrep column may contain null, a single sales rep id, or up to five slaes rep IDs separated by the '~' character.
So I'd like to parse the rep IDs from a single column (salesreplist) in my invoice detail table (below) to multiple columns (RepID1, RepID2, RepID3, RepID4,RepID5) in a temp table so I can more easily calculate the commission amounts for each invoice and sales rep.
Here is my table:
CREATE TABLE invcdtl(
invoicenum int,
salesreplist [text] NULL,
reprate1 int NULL,
reprate2 int NULL,
reprate3 int NULL,
reprate4 int NULL,
reprate5 int NULL,
)
Here is some sample data:
1 A 0 0 0 0 0
2 0 0 0 0 0
3 I~~~~ 15 0 0 0 0
4 A~B 5 5 0 0 0
5 I~F~T~K~G 5 5 2 2 2
6 A~B
As you can see, some records have trailing delimiters but some don't. This may be a result of the application's behavior when multiple reps are entered then removed from an invoice. One thing for sure is that when there are multiple reps, the IDs are always separated by '~'
Can anyone suggest a solution?
View 3 Replies
View Related
Jan 19, 2006
I'm sure there is probably a very easy solution that I am just not seeing or can't Google...
I have a DataFlow that includes a column of Delimited values (i.e. Value1,Value2,etc..). As this DataFlow is populating a parent table, I need split the values into their own dataflow and populate a child table. I've tried a script transformation and couldn't figure out how to accept 1 delimited input row and output multiple rows after a split. Any ideas?
TIA,
Matthew
View 1 Replies
View Related
May 21, 2007
Hi,
I have 2 Tables
Table 1, Row 1
1. Id = 1
2. GraphPoints = 023, 045, 078 (text - data type)
I need to move data to Table 2.
Table 2 should have
1st row
1. Id = 1
2. GraphPoint = 023 (float data type)
2nd row
1. Id = 1
2. GraphPoint = 045 (float data type)
and so on
How do I do that?
Thanks.
View 3 Replies
View Related
May 10, 2007
As part of xml parsing, I use multicast to direct output of nodes to their corresponding relational tables and I do have a comma-delimited list for some nodes which basically needs to be converted into rows as illustrated below
ID Products
--------------------------------------------------------------------------
1 12, 45
2 10, 20
and I would like to have results as
ID Products
--------------------------------------------------------------------------
1 12
1 45
2 10
2 20
I would appreciate if someone could offer me some guidance here.
View 11 Replies
View Related
Jul 9, 2015
All I have a situation where I need to split the column to rows which is delimited using commas and semicolons. Please find the below sample data.
Data in Tables
Test1, Test2, Test3
Test4;Test5;Test6
Desired output
Test1
Test2
Test3
Test4
Test5
Test6
Is there any way that I can get this output in SQL other than using the XML Conversion, since the data has some special characters in this.
View 7 Replies
View Related
Sep 17, 2015
How do i concatenate 24 columns with semicolon delimited into a single column without getting data conversion error ....
View 2 Replies
View Related
Jan 24, 2008
I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time.
What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column?
Thanks,
Steve
View 3 Replies
View Related
