• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1010
  • Last Modified:

Split comma deliminated column in db

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
Small_Balls
Asked:
Small_Balls
2 Solutions
 
chapmandewCommented:
0
 
RiteshShahCommented:
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
 
SharathData EngineerCommented:
where are you getting problem? Are you looking for function which converts comma delimited string to tabular format?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Small_BallsAuthor Commented:
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
 
Small_BallsAuthor Commented:
RiteshShah:

I don't under stand I only know the very basics of SQL
0
 
Jagdish DevakuSr DB ArchitectCommented:
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
 
SharathData EngineerCommented:
Please explain in detail. i dont understand what is your goal from your statement. explain with example if possible.
0
 
Small_BallsAuthor Commented:
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
 
SharathData EngineerCommented:

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
 
SharathData EngineerCommented:
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
 
Small_BallsAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
Small_BallsAuthor Commented:
is there a way that I can do this through C# code?? as I just don't understand the SQL way
0
 
Small_BallsAuthor Commented:
here is a screen shot of the error, it may shed some light on the lssue
eoi-ProjectType-ID-error.jpg
0
 
SharathData EngineerCommented:

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
 
Small_BallsAuthor Commented:
would you like me to post createtable.sql for both the tables ??
0
 
SharathData EngineerCommented:
yes, provide that
0
 
Small_BallsAuthor Commented:
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
 
Small_BallsAuthor Commented:
hello
??????
0
 
Small_BallsAuthor Commented:
Oh sorry I completely overlooked the obvious... as soon as I realized it went together fine... Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now