PATINDEX and CHARINDEX

Hello
I have a table in my database , in one field i have data like this
(230+365+651+695) varchar(100)
I want to use the any SQL function to return the data in form of rows from
the above field.
Result should look like
230
365
651
695
Anyone can help in this regard'?
Thanks in advance.Here's one way to do it using an auxiliary table of numbers:
SET NOCOUNT ON;
USE tempdb; -- specify your user db here
GO
IF OBJECT_ID('dbo.Arrays') IS NOT NULL
DROP TABLE dbo.Arrays;
GO
CREATE TABLE dbo.Arrays
(
arrid VARCHAR(5) NOT NULL PRIMARY KEY,
arr VARCHAR(1000) NOT NULL
);
INSERT INTO dbo.Arrays VALUES('A', '230+365+651+695');
INSERT INTO dbo.Arrays VALUES('B', '1+23+456');
GO
-- Code to create and populate the auxiliary table of numbers:
IF OBJECT_ID('dbo.Nums') IS NOT NULL
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @.max AS INT, @.rc AS INT;
SET @.max = 8000; -- adjust @.max to your needs
SET @.rc = 1;
INSERT INTO dbo.Nums VALUES(1);
WHILE @.rc * 2 <= @.max
BEGIN
INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums;
SET @.rc = @.rc * 2;
END
INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums WHERE n + @.rc <= @.max;
GO
-- Query that splits arrays
SELECT A.arrid,
Nums.n - LEN(REPLACE(LEFT(A.arr, Nums.n), '+', '')) + 1 AS pos,
CAST(SUBSTRING(A.arr, Nums.n,
CHARINDEX('+', A.arr + '+', Nums.n) - Nums.n)
AS INT) AS element
FROM dbo.Arrays AS A
JOIN dbo.Nums
ON Nums.n <= LEN(A.arr) AND SUBSTRING('+' + A.arr, Nums.n, 1) = '+';
GO
Output:
arrid pos element
-- -- --
A 1 230
A 2 365
A 3 651
A 4 695
B 1 1
B 2 23
B 3 456
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"mukki_neo" <u20644@.uwe> wrote in message news:5e741dc37de34@.uwe...
> Hello
> I have a table in my database , in one field i have data like this
> (230+365+651+695) varchar(100)
> I want to use the any SQL function to return the data in form of rows from
> the above field.
> Result should look like
> 230
> 365
> 651
> 695
> Anyone can help in this regard'?
> Thanks in advance.|||Thanks a lot for quick response.
It just work fine with my Database, but what if i have a value of
"Alphanumeric" in this field... like
(DO-903+DP-366+DP-659+TM-TEMP)
same for other rows corresponding columns
i want to show it like
DO-903
DP-366
DP-659
TM-TEMP
Any help please?
Itzik Ben-Gan wrote:
>Here's one way to do it using an auxiliary table of numbers:
>SET NOCOUNT ON;
>USE tempdb; -- specify your user db here
>GO
>IF OBJECT_ID('dbo.Arrays') IS NOT NULL
> DROP TABLE dbo.Arrays;
>GO
>CREATE TABLE dbo.Arrays
>(
> arrid VARCHAR(5) NOT NULL PRIMARY KEY,
> arr VARCHAR(1000) NOT NULL
> );
>INSERT INTO dbo.Arrays VALUES('A', '230+365+651+695');
>INSERT INTO dbo.Arrays VALUES('B', '1+23+456');
>GO
>-- Code to create and populate the auxiliary table of numbers:
>IF OBJECT_ID('dbo.Nums') IS NOT NULL
> DROP TABLE dbo.Nums;
>GO
>CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
>DECLARE @.max AS INT, @.rc AS INT;
>SET @.max = 8000; -- adjust @.max to your needs
>SET @.rc = 1;
>INSERT INTO dbo.Nums VALUES(1);
>WHILE @.rc * 2 <= @.max
>BEGIN
> INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums;
> SET @.rc = @.rc * 2;
>END
>INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums WHERE n + @.rc <= @.max;
>GO
>-- Query that splits arrays
>SELECT A.arrid,
> Nums.n - LEN(REPLACE(LEFT(A.arr, Nums.n), '+', '')) + 1 AS pos,
> CAST(SUBSTRING(A.arr, Nums.n,
> CHARINDEX('+', A.arr + '+', Nums.n) - Nums.n)
> AS INT) AS element
>FROM dbo.Arrays AS A
> JOIN dbo.Nums
> ON Nums.n <= LEN(A.arr) AND SUBSTRING('+' + A.arr, Nums.n, 1) = '+';
>GO
>Output:
>arrid pos element
>-- -- --
>A 1 230
>A 2 365
>A 3 651
>A 4 695
>B 1 1
>B 2 23
>B 3 456
>
>[quoted text clipped - 12 lines]|||I changed the CAST from INT to VARCHAR, and it works fine, am i right?
Itzik Ben-Gan wrote:
>Here's one way to do it using an auxiliary table of numbers:
>SET NOCOUNT ON;
>USE tempdb; -- specify your user db here
>GO
>IF OBJECT_ID('dbo.Arrays') IS NOT NULL
> DROP TABLE dbo.Arrays;
>GO
>CREATE TABLE dbo.Arrays
>(
> arrid VARCHAR(5) NOT NULL PRIMARY KEY,
> arr VARCHAR(1000) NOT NULL
> );
>INSERT INTO dbo.Arrays VALUES('A', '230+365+651+695');
>INSERT INTO dbo.Arrays VALUES('B', '1+23+456');
>GO
>-- Code to create and populate the auxiliary table of numbers:
>IF OBJECT_ID('dbo.Nums') IS NOT NULL
> DROP TABLE dbo.Nums;
>GO
>CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
>DECLARE @.max AS INT, @.rc AS INT;
>SET @.max = 8000; -- adjust @.max to your needs
>SET @.rc = 1;
>INSERT INTO dbo.Nums VALUES(1);
>WHILE @.rc * 2 <= @.max
>BEGIN
> INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums;
> SET @.rc = @.rc * 2;
>END
>INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums WHERE n + @.rc <= @.max;
>GO
>-- Query that splits arrays
>SELECT A.arrid,
> Nums.n - LEN(REPLACE(LEFT(A.arr, Nums.n), '+', '')) + 1 AS pos,
> CAST(SUBSTRING(A.arr, Nums.n,
> CHARINDEX('+', A.arr + '+', Nums.n) - Nums.n)
> AS INT) AS element
>FROM dbo.Arrays AS A
> JOIN dbo.Nums
> ON Nums.n <= LEN(A.arr) AND SUBSTRING('+' + A.arr, Nums.n, 1) = '+';
>GO
>Output:
>arrid pos element
>-- -- --
>A 1 230
>A 2 365
>A 3 651
>A 4 695
>B 1 1
>B 2 23
>B 3 456
>
>[quoted text clipped - 12 lines]|||No need to cast it if you want to keep it a character string.
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"mukki_neo" <u20644@.uwe> wrote in message news:5e757a18834f7@.uwe...
>I changed the CAST from INT to VARCHAR, and it works fine, am i right?
> Itzik Ben-Gan wrote: