January 21, 2016 at 5:36 am
I want to generate output table [temp_IMS_Output] using input table [temp_IMS]
Please see the script below
CREATE TABLE [temp_IMS](
[listpos] [int] NOT NULL,
[str] [varchar](4000) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [temp_IMS] ([listpos], [str]) VALUES (1, N'552:0')
INSERT [temp_IMS] ([listpos], [str]) VALUES (2, N'538:1')
GO
CREATE TABLE [temp_IMS_Output](
[listpos] [int] NOT NULL,
[str] [varchar](4000) NULL,
[str2] [varchar](4000) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [temp_IMS_Output] ([listpos], [str],[str2]) VALUES (1, N'552','0')
INSERT [temp_IMS_Output] ([listpos], [str],[str2]) VALUES (2, N'538','1')
select * from [temp_IMS]
select * from [temp_IMS_Output]
January 21, 2016 at 5:51 am
What have you tried so far? You could lookup the functions CHARINDEX and SUBSTRING in Books Online to get started towards a solution.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 21, 2016 at 6:01 am
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
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 "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) 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 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
Use it like this (if it's always 2 items - which is what it appears to be in your test data): -
SELECT [listpos],
MAX(CASE WHEN ca.[ItemNumber] = 1 THEN [ca].[Item]
ELSE ''
END) AS [str],
MAX(CASE WHEN ca.[ItemNumber] = 2 THEN [ca].[Item]
ELSE ''
END) AS [str2]
FROM [dbo].[temp_IMS]
CROSS APPLY [dbo].[DelimitedSplit8K]([str], ':') ca
GROUP BY [listpos];
January 21, 2016 at 6:22 am
As an aside, do you control the design of these tables? Typically it is not a good practice to store delimited strings in your database. What kind of data is this representing and is the source table a permanent table or simply a staging table?
One more question, will the source data always have a single delimiter or can you have strings with multiple delimiters where you will only ever need the first two values?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 21, 2016 at 9:58 am
A solution could be:
SELECT
listpos
, substring(str,1, PATINDEX ( '%:%' , str )-1) str
, substring(str, PATINDEX ( '%:%' , str )+1, 999) str2
INTO [temp_IMS_Output]
FROM [temp_ims]
SELECT * FROM [temp_IMS_Output]
This is limited to only two values seperated with a ':' in the string.
Please inform us if this was sufficient for your problem.
Ben
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply