How to split a comma-separated value to columns
CREATE FUNCTION [dbo].[fn_split_string_to_column] (
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @out_put TABLE (
[column_id] INT IDENTITY(1, 1) NOT NULL,
[value] NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @value NVARCHAR(MAX),
@pos INT = 0,
@len INT = 0
SET @string = CASE
WHEN RIGHT(@string, 1) != @delimiter
THEN @string + @delimiter
ELSE @string
END
WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
BEGIN
SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
SET @value = SUBSTRING(@string, @pos, @len)
INSERT INTO @out_put ([value])
SELECT LTRIM(RTRIM(@value)) AS [column]
SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
END
RETURN
END
T-SQL split string
I've used this SQL before which may work for you:-
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
and to use it:-
SELECT * FROM dbo.splitstring('91,12,65,78,56,789')
How do I split a delimited string so I can access individual items?
You may find the solution in SQL User Defined Function to Parse a Delimited String helpful (from The Code Project).
You can use this simple logic:
Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null
WHILE LEN(@products) > 0
BEGIN
IF PATINDEX('%|%', @products) > 0
BEGIN
SET @individual = SUBSTRING(@products,
0,
PATINDEX('%|%', @products))
SELECT @individual
SET @products = SUBSTRING(@products,
LEN(@individual + '|') + 1,
LEN(@products))
END
ELSE
BEGIN
SET @individual = @products
SET @products = NULL
SELECT @individual
END
END
STRING_SPLIT in SQL Server 2012
Other approach is to use XML
Method with CROSS APPLY
to split your Comma Separated Data :
SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
FROM
(
SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a);
Result :
DATA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Example :
DECLARE @ID NVARCHAR(300)= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';
DECLARE @Marks NVARCHAR(300)= '0,1,2,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0';
DECLARE @StudentsMark TABLE
(id NVARCHAR(300),
marks NVARCHAR(300)
);
--insert into @StudentsMark
;WITH CTE
AS (
SELECT Split.a.value('.', 'NVARCHAR(MAX)') id,
ROW_NUMBER() OVER(ORDER BY
(
SELECT NULL
)) RN
FROM
(
SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a)),
CTE1
AS (
SELECT Split.a.value('.', 'NVARCHAR(MAX)') marks,
ROW_NUMBER() OVER(ORDER BY
(
SELECT NULL
)) RN
FROM
(
SELECT CAST('<X>'+REPLACE(@Marks, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a))
INSERT INTO @StudentsMark
SELECT C.id,
C1.marks
FROM CTE C
LEFT JOIN CTE1 C1 ON C1.RN = C.RN;
SELECT *
FROM @StudentsMark;
How do I split a string into 2 parts based on a delimiter?
Here is a simple function that could be used to split string in DB:
SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');
it will return all the values by splitting with space.
Split String into given length but do not split word
Assuming you have a table of addresses, I'd use a recursive CTE.
On each iteration, find the last possible space to break on, then start the next iteration for the character after the space.
- take 31 characters
- reverse them
- find the position of the first space
Extra care to be taken for corner cases:
- The remaining string to be searched is less than 30 characters
- The current string being searched has no space in the first 31 characters
Using the following test data...
CREATE TABLE test (
address VARCHAR(MAX)
);
INSERT INTO
test
VALUES
('216 Apartment123 AreaArea SampleWord1 Word2 MiddleTown Upper1Location Another5 NewYork'),
('216 Apartment123 AreaArea SampleWord1 Word2 MiddleTownxx Upper1LocationUpper1LocationUpper1Location Another5 NewYork'),
('216 Apartment123 AreaArea SampleWord1 Word2 MiddleTownxx Upper1LocationUpper1LocationUpper1Location Another5 NewYork x')
;
Using the following CTE...
DECLARE @chars BIGINT = 30;
WITH
parts AS
(
SELECT
address,
LEN(address) AS length,
CAST(0 AS BIGINT) AS last_space,
CAST(1 AS BIGINT) AS next,
address AS fragment
FROM
test
UNION ALL
SELECT
parts.address,
parts.length,
last_space.pos,
parts.next + COALESCE(last_space.pos, @chars),
SUBSTRING(parts.address, parts.next, COALESCE(last_space.pos - 1, @chars))
FROM
parts
CROSS APPLY
(
SELECT
@chars + 2
-
NULLIF(
CHARINDEX(
' ',
REVERSE(
SUBSTRING(
parts.address + ' ',
parts.next,
@chars + 1
)
)
)
, 0
)
)
last_space(pos)
WHERE
parts.next <= parts.length
)
SELECT
*, len(fragment) AS chars
FROM
parts
WHERE
next > 1
ORDER BY
address,
next
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=acd11f2bc73e5036bd82498ecf14b08f
Related Topics
Oracle: SQL Query That Returns Rows with Only Numeric Values
Postgresql Query to Count/Group by Day and Display Days with No Data
Create SQL Insert Script with Values Gathered from Table
Add Row to Query Result Using Select
Fastest Way to Export Blobs from Table into Individual Files
How to Use Order by with Union All in SQL
How to Clear Oracle Execution Plan Cache for Benchmarking
How to Analyse a SQLite Query Execution
Why Should I Capitalize My SQL Keywords
How to Do a Simple 'Find and Replace" in Mssql
Calling a Stored Procedure in Oracle with in and Out Parameters
Exporting Blob from MySQL Database to File with Only SQL
How to Correctly Do Upsert in Postgres 9.5
How to Find the Record in a Table That Contains the Maximum Value