Solved

t-sql help

Posted on 2016-09-14
5
46 Views
Last Modified: 2016-09-14
DECLARE @TEMP TABLE
(
      ServiceID         VARCHAR(10),
      ServiceType      VARCHAR(10)
)

DECLARE  @ServiceID_In            VARCHAR(200);
SET @ServiceID_In = '136:PPO,136:HMO,365:HMO';

SELECT  @ServiceID  ServiceID;


I’m looking output from @TEMP table like:
ServiceID      ServiceType
136            PPO
136            HMO
365            HMO

Thank you very much for help!
0
Comment
Question by:jfreeman2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 41798533
This will give you what you want based on splitting first off the comma (using a function) and then using the colon as a charindex:
-- insert into @TEMP
select SUBSTRING(item, 0,charindex(':',item)) ServiceID, 
SUBSTRING(item,charindex(':',item)+1, len(item) - charindex(':', item)+1) ServiceType  from dbo.fn_Txt_Split(@serviceid_in, ',')
 

Open in new window


This is the code for fn_txt_split . . . note not my function, just a splitter I use that works well:
/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 09/14/2016 14:19:22 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Txt_Split]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_Txt_Split]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 09/14/2016 14:19:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




Create Function [dbo].[fn_Txt_Split]( 
    @sInputList varchar(8000) -- List of delimited items 
  , @Delimiter char(1) = ',' -- delimiter that separates items 
) 
RETURNS @list table (Item varchar(8000)) 
as begin 
DECLARE @Item Varchar(8000) 
  
  

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 



GO

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41798566
You;'ll need a "standard" string-splitter function.  DelimitedSplit8K has great performance, even across a very large number of rows.  Note that for any type of really decent performance, you'll need an inline-table-valued function, rather than a multi-statement-table-valued function.

First create the function below, just one time, then you can run this code

DECLARE  @ServiceID_In            VARCHAR(200);
SET @ServiceID_In = '136:PPO,136:HMO,365:HMO';

SELECT LEFT(Item, CHARINDEX(':', Item) - 1) AS ServiceID, RIGHT(Item, CHARINDEX(':', REVERSE(Item)) - 1) AS ServiceType
FROM dbo.DelimitedSplit8K(@ServiceID_In, ',')

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.DelimitedSplit8K (
    @pString varchar(8000),
    @pDelimiter char(1)
    )
RETURNS TABLE WITH SCHEMABINDING 
AS
/*SELECT * FROM dbo.DelimitedSplit8K('ab/c/def/ghijklm/no/prq/////st/u//', '/')*/
RETURN
/*Inline CTE-driven "tally table" produces values from 0 up to 10,000: enough to cover varchar(8000).*/
WITH E1(N) AS (SELECT N FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Ns(N)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max 
    ctetally(N) AS (/* This provides the "zero base" and limits the number of rows right up front,
                       for both a performance gain and prevention of accidental "overruns". */
        SELECT 0 UNION ALL
        SELECT TOP (DATALENGTH(ISNULL(@pString, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    ), cteStart(N1) AS ( /* This returns N+1 (starting position of each "element" just once for each delimiter). */
        SELECT t.N+1
        FROM ctetally t
        WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
    )
/* Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. */
SELECT ROW_NUMBER() OVER(ORDER BY s.N1) AS ItemNumber,
       SUBSTRING(@pString, s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1), 0) - s.N1, 8000)) AS Item
FROM cteStart s;
GO

Open in new window

0
 

Author Closing Comment

by:jfreeman2010
ID: 41798572
Thank you very much for help!!
0
 
LVL 34

Expert Comment

by:ste5an
ID: 41798587
SQL Server 2016 has a build-in function: STRING_SPLIT().
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41798789
Yes, but be aware of the limitations/restrictions, based on testing earlier this year by Aaron Bertrand.

1) the function requires the database to be in compatibility level 130; even MS states that by default some Azure dbs might be 120

2) there is no way to control sorting – the only options are arbitrary and alphabetical ORDER BY value -- many people require that if they input 'a,r,f,q' they get a r f and q back in that order, but not guaranteed for SPLIT_STRING

3) there is no way to add output columns (like a column indicating ordinal position within the string);

4) there is no way to push down predicates, like eliminating duplicates or empty strings due to consecutive delimiters
1

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

738 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