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:

PASSWORD PROTECTED TABLE

Hello:
I would like to know if there is a way to get a password protected table in
sqlserver 2000, developer EDITION
The database administrator can open any table including a user table and he
can know the password of any user just by opening the table, that's why is
better to have a table with password protected or any encryption type in the
password field associated to the login, so only the IT manager can open this
table and not any programmer that has access to SQLserver.
ThanksThere are third party products that offer encryption
functionality. You can find them listed in this FAQ site in
the encryption section:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
-Sue
On Fri, 14 Oct 2005 15:44:03 -0400, "Gina Hernandez"
<pdwhitt@.nospam.wdsinc.com> wrote:

>Hello:
>I would like to know if there is a way to get a password protected table in
>sqlserver 2000, developer EDITION
>The database administrator can open any table including a user table and h
e
>can know the password of any user just by opening the table, that's why i
s
>better to have a table with password protected or any encryption type in th
e
>password field associated to the login, so only the IT manager can open thi
s
>table and not any programmer that has access to SQLserver.
>
>Thanks
>