Solved

Split comma deliminated column in db

Posted on 2009-05-06
20
956 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
Comment Utility
0
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
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
Comment Utility
where are you getting problem? Are you looking for function which converts comma delimited string to tabular format?
0
 

Author Comment

by:Small_Balls
Comment Utility
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
Comment Utility
RiteshShah:

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

Expert Comment

by:Jagdish Devaku
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
would you like me to post createtable.sql for both the tables ??
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
yes, provide that
0
 

Author Comment

by:Small_Balls
Comment Utility
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
Comment Utility
hello
??????
0
 

Author Closing Comment

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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 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

10 Experts available now in Live!

Get 1:1 Help Now