Solved

Split comma deliminated column in db

Posted on 2009-05-06
20
960 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
 

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

910 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

16 Experts available now in Live!

Get 1:1 Help Now