"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.

>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
>
>> Hello
>>
>[quoted text clipped - 12 lines]
>> 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
>
>> Hello
>>
>[quoted text clipped - 12 lines]
>> Anyone can help in this regard?????
>> Thanks in advance.

"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:
>>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
>>
>>> Hello
>>>
>>[quoted text clipped - 12 lines]
>>> Anyone can help in this regard?????
>>> Thanks in advance.