Solved

Split comma deliminated column in db

Posted on 2009-05-06
20
964 Views
Last Modified: 2012-05-06
I have a nvarchar column that contains some comma deliminated data eg 1,12,35,6. I need to split the ones that are comma deliminated in order to query with a look up table that contains the text version of the number..

but I cant seem to get it right.even if it vreats another table that contains the the EOI_Enquiry_ID (to remain relational) and ProjectType_ID columns so I can link that table??

any help will be most appreciated

Trent


ALTER FUNCTION CMA_projects.SplitString (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO
 
as for the query
 
WITH CTE AS (SELECT     ProjectType_ID
                               FROM          CMA_projects.customers_data_0_enquiries
                               WHERE      (ProjectType_ID IS NOT NULL))
    SELECT     ProjectType_ID
     FROM         CTE AS CTE_1
CROSS APPLY CMA_projects.SplitString(ProjectType_ID,',')

Open in new window

0
Comment
Question by:Small_Balls
20 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24321811
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24321926
you need help of COALESCE function. have a look at here

http://www.sqlhub.com/2008/04/coalesce-function-in-sql-server-for.html
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24322163
where are you getting problem? Are you looking for function which converts comma delimited string to tabular format?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Small_Balls
ID: 24322506
Sharath_123:
"EOI_Enquiry_ID (to remain relational) and ProjectType_ID columns so I can link that table??"

I need to incorperate the table into another query so I can attach a look up
0
 

Author Comment

by:Small_Balls
ID: 24323517
RiteshShah:

I don't under stand I only know the very basics of SQL
0
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24323739
check the below query in the snippet...

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23861373.html

please let me know if the below helps you or not...

declare @a varchar(max)
set @a='a,b,c,d,e,f,g,h,i,j,';
 
with a as
 (select charindex(',', @a) as ix, substring(@a, 1, charindex(',', @a) - 1) as txt,substring(@a, charindex(',', @a) + 1, len(@a)) as txt2
  union all
  select charindex(',',@a, t1.ix+1), substring (t1.txt2, 1, charindex(',',txt2)-1), substring(txt2, charindex(',',t1.txt2)+1, len(t1.txt2))
   from a t1
  where charindex(',', txt2) > 0
)
 
select txt from a
--OPTION (MAXRECURSION 10000)
;

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 24340523
Please explain in detail. i dont understand what is your goal from your statement. explain with example if possible.
0
 

Author Comment

by:Small_Balls
ID: 24361670
Think of it this way.. I have a column in my sql 2005 database that is storing
eg:

current

CustomerID   CustomerName    ProjectTypeid    ProjectTypeName(lookup table)

1                 John smith         1                     Preannials
1                 John smith         1,57,42            error

What I need it to do is split the comma seperated field whilst still being relational to the parrent and child tables and convert the column to int

Required

CustomerID   CustomerName    ProjectTypeid    ProjectTypeName

1                 John smith         1                     Preannials
1                 John smith         1                     Preannials
1                 John smith         57                   Bio Banking
1                 John smith         42                   Soil Conservation

keeping in mind that not all rows have commas

the reason for this is beacuse I need to link the ProjectType_ID column with a look up table that has the same column name..

I hope this makes sense
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 24370377

Now i understood, create a function like the attached script. call this function in your query like the below example.

select t1.CustomerID,t1.CustomerName,t2.entry as ProjectTypeId,t3.ProjectTypeName
from MainTable t1
cross apply dbo.split(ProjectTypeid,',') t2
left join LookupTable t3 on convert(int,t2.Entry)= t3.ProjectTypeId

CREATE FUNCTION [dbo].[split] 
       (@csv nvarchar(max), 
        @delim varchar(1))
        RETURNS @entries TABLE (entry nvarchar(100))
    AS BEGIN    
       DECLARE @commaindex int    
        SELECT @commaindex = CHARINDEX(@delim, @csv)    
            IF @commaindex > 0        
               BEGIN            
               INSERT INTO @entries -- insert left side            
               SELECT LTrim(RTrim(LEFT(@csv, @commaindex-1)))            -- pass right side recursively            
               UNION ALL            
               SELECT entry           
               FROM dbo.split(RIGHT(@csv, LEN(@csv) - @commaindex), @delim)           
               END    
          ELSE BEGIN            
               INSERT INTO @entries            
               SELECT LTrim(RTrim(@csv))       
               END    RETURN
           END
GO

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 24370385
tested that. see the attached code.
declare @MainTable table(CustomerID int,CustomerName nvarchar(100),ProjectTypeid nvarchar(100))
declare @LookupTable table(ProjectTypeId int,ProjectTypeName nvarchar(100))
insert @MainTable values (1,'John smith','1')
insert @MainTable values (1,'John smith','1,57,42')
insert into @LookupTable values (1,'Preannials'),(57,'Bio Banking'),(42,'Soil Conservation')
 
-- You have this.
select *
  from @MainTable t1
  left join @LookupTable t2 on t1.ProjectTypeid = convert(varchar,t2.ProjectTypeId)
 
CustomerID	CustomerName	ProjectTypeid	ProjectTypeId	ProjectTypeName
1	         John smith	1	         1	          Preannials
1	         John smith	1,57,42	         NULL	          NULL
  
-- You want this  
select t1.CustomerID,t1.CustomerName,t2.entry as ProjectTypeId,t3.ProjectTypeName
 from @MainTable t1
 cross apply dbo.split(ProjectTypeid,',') t2
  left join @LookupTable t3 on convert(int,t2.Entry)= t3.ProjectTypeId
 
CustomerID	CustomerName	ProjectTypeId	ProjectTypeName
1	John smith	1	Preannials
1	John smith	1	Preannials
1	John smith	57	Bio Banking
1	John smith	42	Soil Conservation

Open in new window

0
 

Author Comment

by:Small_Balls
ID: 24370610
insert into @LookupTable values (1,'Preannials'),(57,'Bio Banking'),(42,'Soil Conservation')... um I wont know all values as it is editable.... can this declare function just look at the lookup table to get the info required??
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 500 total points
ID: 24372164
The last post was an example to illustrate your scenario. Please refer post#24370377.
Create that function and run the below query.
select t1.CustomerID,t1.CustomerName,t2.entry as ProjectTypeId,t3.ProjectTypeName
from MainTable t1
cross apply dbo.split(ProjectTypeid,',') t2
left join LookupTable t3 on convert(int,t2.Entry)= t3.ProjectTypeId  
You can replace the column/table names as per your actual column/table names. The column entry (highlighted) is return column name from the funtion, so don't change that.
Hope you understand.
0
 

Author Comment

by:Small_Balls
ID: 24420070
is there a way that I can do this through C# code?? as I just don't understand the SQL way
0
 

Author Comment

by:Small_Balls
ID: 24420334
here is a screen shot of the error, it may shed some light on the lssue
eoi-ProjectType-ID-error.jpg
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24476346

Provide the table/column structre of your two tables? I am a SQL guy and don't know about C#. I am not getting any error. May be the i can troubleshoot with columns data type and some sample data from your tables.
0
 

Author Comment

by:Small_Balls
ID: 24476520
would you like me to post createtable.sql for both the tables ??
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24476612
yes, provide that
0
 

Author Comment

by:Small_Balls
ID: 24477066
here are the scripts you will need to create a db called CMA_projects
/*customers_data_0_enquiries*/
 
USE [CMA_projects]
GO
 
/****** Object:  Table [CMA_projects].[customers_data_0_enquiries]    Script Date: 05/27/2009 06:05:52 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [CMA_projects].[customers_data_0_enquiries](
	[EOI_Enquiry_ID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [int] NULL,
	[Information_Already] [tinyint] NULL,
	[CustomerStatus] [tinyint] NULL,
	[EOI_Enquiry_Status_ID] [int] NULL,
	[ProjectType_ID] [nvarchar](100) NULL,
	[TrainingStatusID] [int] NULL,
	[EOI_Enquiry_Notes] [nvarchar](max) NULL,
	[Mailout_Requested] [tinyint] NULL,
	[MailoutSentDate] [datetime] NULL,
	[MailoutSentBy_UserID] [int] NULL,
	[NewsletterRequested] [tinyint] NULL,
	[NewsletterSentDate] [datetime] NULL,
	[NewsletterSentBy_UserID] [int] NULL,
	[IPSurveyID] [int] NULL,
	[IPSurvey_SubTypes] [int] NULL,
	[EOI_Enquiry_UpdatedBy] [nvarchar](255) NULL,
	[EOI_Enquiry_UpdatedDate] [datetime] NULL,
	[xHeaderIDLink] [int] NULL,
	[xApplicantLink] [int] NULL,
	[xPropertyLink] [int] NULL,
	[xFlagshipID] [int] NULL,
	[x_IDProject] [float] NULL,
	[x_ContactsID] [float] NULL,
	[SSMA_TimeStamp] [timestamp] NOT NULL,
 CONSTRAINT [customers_data_0_enquiries$PrimaryKey] PRIMARY KEY CLUSTERED 
(
	[EOI_Enquiry_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [CMA_projects].[customers_data_0_enquiries]  WITH NOCHECK ADD  CONSTRAINT [customers_data_0_enquiries${14F39814-C091-427D-B5AA-D918FBDDA749}] FOREIGN KEY([CustomerID])
REFERENCES [CMA_projects].[customers_data_0_details] ([CustomerID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
 
ALTER TABLE [CMA_projects].[customers_data_0_enquiries] CHECK CONSTRAINT [customers_data_0_enquiries${14F39814-C091-427D-B5AA-D918FBDDA749}]
GO
 
ALTER TABLE [CMA_projects].[customers_data_0_enquiries]  WITH NOCHECK ADD  CONSTRAINT [customers_data_0_enquiries${702614B9-F644-4990-A6A6-E0B8D6CC869B}] FOREIGN KEY([TrainingStatusID])
REFERENCES [CMA_projects].[eoi_lut_status_pre_training] ([TrainingStatusID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
 
ALTER TABLE [CMA_projects].[customers_data_0_enquiries] CHECK CONSTRAINT [customers_data_0_enquiries${702614B9-F644-4990-A6A6-E0B8D6CC869B}]
GO
 
ALTER TABLE [CMA_projects].[customers_data_0_enquiries]  WITH NOCHECK ADD  CONSTRAINT [customers_data_0_enquiries${97EB68DE-7910-4B4E-AD37-06AEBC79EB91}] FOREIGN KEY([EOI_Enquiry_Status_ID])
REFERENCES [CMA_projects].[eoi_lut_status_enquiry] ([EOI_Enquiry_Status_ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
 
ALTER TABLE [CMA_projects].[customers_data_0_enquiries] CHECK CONSTRAINT [customers_data_0_enquiries${97EB68DE-7910-4B4E-AD37-06AEBC79EB91}]
GO
 
ALTER TABLE [CMA_projects].[customers_data_0_enquiries] ADD  DEFAULT ((0)) FOR [Information_Already]
GO
 
ALTER TABLE [CMA_projects].[customers_data_0_enquiries] ADD  DEFAULT ((0)) FOR [CustomerStatus]
GO
 
ALTER TABLE [CMA_projects].[customers_data_0_enquiries] ADD  DEFAULT ((0)) FOR [Mailout_Requested]
GO
 
ALTER TABLE [CMA_projects].[customers_data_0_enquiries] ADD  DEFAULT ((0)) FOR [NewsletterRequested]
GO
 
/*project_lut_project_type*/
 
USE [CMA_projects]
GO
 
/****** Object:  Table [CMA_projects].[project_lut_project_type]    Script Date: 05/27/2009 06:06:35 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [CMA_projects].[project_lut_project_type](
	[ProjectType_ID] [int] IDENTITY(1,1) NOT NULL,
	[IMPSID] [int] NULL,
	[CAPProgramID] [int] NULL,
	[ACTIVITY_CATEGORY_CODE] [int] NULL,
	[ACTIVITY_CODE] [int] NULL,
	[ACTIVITY_TYPE_SUB_TYPE_ID] [int] NULL,
	[Project_Type_Name] [nvarchar](255) NULL,
	[Project_Type_Code] [nvarchar](10) NULL,
	[BranchID] [int] NULL,
	[Multiplier] [float] NULL,
	[CMAID] [int] NULL,
	[Available] [tinyint] NULL,
	[ProjectImageSample] [nvarchar](100) NULL,
	[ProjectType_UpdatedBy] [nvarchar](50) NULL,
	[ProjectType_UpdatedDate] [datetime] NULL,
	[SSMA_TimeStamp] [timestamp] NOT NULL,
 CONSTRAINT [project_lut_project_type$PrimaryKey] PRIMARY KEY CLUSTERED 
(
	[ProjectType_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [CMA_projects].[project_lut_project_type] ADD  DEFAULT ((0)) FOR [IMPSID]
GO
 
ALTER TABLE [CMA_projects].[project_lut_project_type] ADD  DEFAULT ((0)) FOR [CAPProgramID]
GO
 
ALTER TABLE [CMA_projects].[project_lut_project_type] ADD  DEFAULT ((0)) FOR [ACTIVITY_CATEGORY_CODE]
GO
 
ALTER TABLE [CMA_projects].[project_lut_project_type] ADD  DEFAULT ((0)) FOR [ACTIVITY_CODE]
GO
 
ALTER TABLE [CMA_projects].[project_lut_project_type] ADD  DEFAULT ((0)) FOR [ACTIVITY_TYPE_SUB_TYPE_ID]
GO
 
ALTER TABLE [CMA_projects].[project_lut_project_type] ADD  DEFAULT ((0)) FOR [BranchID]
GO
 
ALTER TABLE [CMA_projects].[project_lut_project_type] ADD  DEFAULT ((0)) FOR [Multiplier]
GO
 
ALTER TABLE [CMA_projects].[project_lut_project_type] ADD  DEFAULT ((0)) FOR [CMAID]
GO

Open in new window

0
 

Author Comment

by:Small_Balls
ID: 24508019
hello
??????
0
 

Author Closing Comment

by:Small_Balls
ID: 31578837
Oh sorry I completely overlooked the obvious... as soon as I realized it went together fine... Thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now