section a string
Below is the function.
ALTER FUNCTION [dbo].[L_SPLIT]
(
@P1 varchar(max), -- string to parse
@P2 int, -- number of sections
@P3 varchar(10),-- Delimiter
@P4 int –- section to return
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
Declare @products varchar(Max)
DECLARE @cleanInput varchar(max)
Declare @TEST VARCHAR(10)
Declare @I int
set @I =1
SET @TEST = '%'+ @P3+'%'
set @products = @P1
--WHILE LEN(@products) > 0
WHILE @I <= @P4 and @I <= @P2
BEGIN
SET @cleanInput = SUBSTRING(@products,
0,
PATINDEX(@TEST, @products))
SET @products = SUBSTRING(@products,
LEN(@cleanInput + @P2) + 1,
LEN(@products))
set @I = @I + 1
END
set @cleanInput = CASE
WHEN @P4 < @P2 THEN @cleanInput
Else @products
end
-- Return the result of the function
RETURN @cleanInput
END
When I Run
select
dbo.L_SPLIT('1|20|3|343|44
it returns 1 which is correct
when I run
select
dbo.L_SPLIT('1|20|3|343|44
it returns NULL and it should be 20
when I run select
dbo.L_SPLIT('1|20|3|343|44
It returns |3|343|44|6|8765 it should be 3|343|44|6|8765
Sr. System Analyst
select dbo.L_SPLIT('1|20|3|343|44|6|8765',3,'|',1)
it returns 1 which is correct
select dbo.L_SPLIT('1|20|3|343|44|6|8765',3,'|',2)
it returns NULL and it should be 20
select dbo.L_SPLIT('1|20|3|343|44|6|8765',3,'|',3)
It returns |3|343|44|6|8765 it should be 3|343|44|6|8765
for example this
select dbo.L_SPLIT('1|20|3|343|44|6|8765',3,'|',3)
It returns |3|343|44|6|8765 it should be 3|343|44|6|8765
Data Engineer
when I run select
dbo.L_SPLIT('1|20|3|343|44
It returns |3|343|44|6|8765 it should be 3|343|44|6|8765 >>
Why it should return 3|343|44|6|8765 and why not just 3?
What is the purpose of 2nd parameter (i.e. sections) in your string?
This function splits the string on the passed delimiter and returns the value at desired location.
CREATE FUNCTION splitString
(@p1 varchar(max),
@p2 varchar(1),
@p3 int
)
RETURNS VARCHAR(100) AS
BEGIN
DECLARE @string VARCHAR(100)
;WITH ToXml AS
(
SELECT CAST(('<td><![CDATA[' + REPLACE(@p1, @p2, ']]></td><td><![CDATA[') + ']]></td>') AS XML) AS XmlData
),
CTE AS (
SELECT td.value('.', 'NVARCHAR(255)') AS [value],
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rn
FROM ToXml
CROSS APPLY XmlData.nodes('td') A ( td ))
SELECT @string = value FROM CTE WHERE rn = @p3
RETURN @string
END
GO
SELECT dbo.splitString('1|20|3|343|44|6|8765','|',1); --1
SELECT dbo.splitString('1|20|3|343|44|6|8765','|',2); --20
SELECT dbo.splitString('1|20|3|343|44|6|8765','|',3); --3
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions