The original data
ID NAME
69213582656566664 5134 56690027015 test text
39213582.6566566635:134569690027015, 78 test text



Want

ID NAME
69213582 test text
656656666645 test text
13456 test text
69010275015 test text
39213582 test text
6566566635 test text
134569 test text
6901027501578 test text

CodePudding user response:

Essentially a version in 2012 and above can use STRING_SPLIT function, if there is no need to write a
The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T ([ID] nvarchar (60), [NAME] nvarchar (24)) 
Insert # T 
Select N '69213582656566664, 5134, 56690027015', N 'test text' union all 
Select N '39213582.6566566635:134569690027015, 78', N 'test text' 
Go 
- the end of the test data 
SELECT tt. Value, 
The NAME 
The FROM # T 
CROSS the APPLY 
(SELECT * FROM STRING_SPLIT (ID, ', ') t) tt; 

CodePudding user response:

 USE tempdb for 
GO 
IF OBJECT_ID (' dbo. [t]) IS NOT NULL 
DROP TABLE dbo. [t] 
GO 
The CREATE TABLE dbo. [t] (
[ID] NVARCHAR (100) 
, [NAME] NVARCHAR (20) 
) 
GO 
SET NOCOUNT ON 
INSERT INTO dbo. [t] VALUES (', 69213582656566664, 5134, 56690027015 N, N 'test text) 
INSERT INTO dbo. [t] VALUES (N '39213582.6566566635:134569690027015, 78', N 'test text) 
- above for testing table and test data - 
GO 
IF OBJECT_ID (' [dbo]. [Fun_Split] ') IS NOT NULL 
The DROP FUNCTION [dbo] [Fun_Split] 
GO 
-=============================================
Author: yenange 
- the Create date: 2014-03-04 
- Description: segmentation string 
Example: SELECT * FROM [dbo] [Fun_Split] (a, b, d, c ', ', ') 
-=============================================
The CREATE FUNCTION [dbo] [Fun_Split] 
(
@ STR NVARCHAR (MAX), 
@ the separator NVARCHAR (MAX) 
) 
RETURNS the TABLE 
AS 
RETURN 
(
The SELECT ROW_NUMBER () OVER (order by (SELECT 0)) AS rowNum, B.i d 
The FROM (
SELECT [value]=the CONVERT (XML, '& lt; V> '+ REPLACE (@ STR, @ the separator,' & lt;/v>  '+' & lt;/v> ') 
) A 
The OUTER APPLY (
SELECT id=N.V.V alue (', 'nvarchar (Max)') 
The FROM a. [value]. Nodes ('/v) N (v) 
B) 
WHERE ISNULL (B.i d, ' ')! 
=' ') 
GO 

Query - 
SELECT the AS f.i d ID, t.N AME 
The FROM t CROSS the APPLY dbo. Fun_Split (REPLACE (REPLACE (ID, '. ', ', '), ':', ', '), ', ') AS f 

CodePudding user response:

reference 1/f, February 16 response:

used version in 2012 and above can use STRING_SPLIT function, if there is no need to write a
The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T ([ID] nvarchar (60), [NAME] nvarchar (24)) 
Insert # T 
Select N '69213582656566664, 5134, 56690027015', N 'test text' union all 
Select N '39213582.6566566635:134569690027015, 78', N 'test text' 
Go 
- the end of the test data 
SELECT tt. Value, 
The NAME 
The FROM # T 
CROSS the APPLY 
(SELECT * FROM STRING_SPLIT (ID, ', ') t) tt; 

thank you

CodePudding user response:

refer to the second floor of gypsy song response:

 USE tempdb for 
GO 
IF OBJECT_ID (' dbo. [t]) IS NOT NULL 
DROP TABLE dbo. [t] 
GO 
The CREATE TABLE dbo. [t] (
[ID] NVARCHAR (100) 
, [NAME] NVARCHAR (20) 
) 
GO 
SET NOCOUNT ON 
INSERT INTO dbo. [t] VALUES (', 69213582656566664, 5134, 56690027015 N, N 'test text) 
INSERT INTO dbo. [t] VALUES (N '39213582.6566566635:134569690027015, 78', N 'test text) 
- above for testing table and test data - 
GO 
IF OBJECT_ID (' [dbo]. [Fun_Split] ') IS NOT NULL 
The DROP FUNCTION [dbo] [Fun_Split] 
GO 
-=============================================
Author: yenange 
- the Create date: 2014-03-04 
- Description: segmentation string 
Example: SELECT * FROM [dbo] [Fun_Split] (a, b, d, c ', ', ') 
-=============================================
The CREATE FUNCTION [dbo] [Fun_Split] 
(
@ STR NVARCHAR (MAX), 
@ the separator NVARCHAR (MAX) 
) 
RETURNS the TABLE 
AS 
RETURN 
(
The SELECT ROW_NUMBER () OVER (order by (SELECT 0)) AS rowNum, B.i d 
The FROM (
SELECT [value]=the CONVERT (XML, '& lt; V> '+ REPLACE (@ STR, @ the separator,' & lt;/v>  '+' & lt;/v> ') 
) A 
The OUTER APPLY (
SELECT id=N.V.V alue (', 'nvarchar (Max)') 
The FROM a. [value]. Nodes ('/v) N (v) 
B) 
WHERE ISNULL (B.i d, ' ')! 
=' ') 
GO 

Query - 
SELECT the AS f.i d ID, t.N AME 
nullnullnullnullnullnullnullnullnullnull