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:

Passing text parameters

Hello Guys,

create procedure spsCheckMsg(
@.sTyp as varchar(100),
@.tMsg as text) as
select @.tMsg = md_body from tbl_msgdef where msgname = @.sTyp

Parameters of the type text can be used in stored procedures. So I wanted to get the text data (md_body) by a select statement and pass it to the parameter @.tMsg. This code doesn't work. I always get the message "The assignment operation can't have a text datatype as argument" which refers to the select statement.

Does anyone have a solution for this?Howdy

If I have understood correctly, try :

-------------------
Create Procedure spsCheckMsg
(
@.sTyp as varchar(1000),
@.tMsg as varchar(1000)
)

AS

set @.tMsg =
( select convert(varchar(1000),md_body) from tbl_msgdef where msgname = @.sTyp )
-------------------

Cheers,

SG

passing table name as parameter for SP - asap

Hi all,
i have SP where i need to pass the tablename and the date as the
parameter
i started like this
CREATE PROC SP_DEL_STG_TAB
@.TABLE_NAME varchar(50) ,
@.PRESENT_REC_LD_DT DATETIME
AS
EXEC ( 'DELETE FROM '+ @.TABLE_NAME +' WHERE LOAD_DT =' +
@.PRESENT_REC_LD_DT)
GO
when i complied i did not find error but during the execution by passing
the parameters using
EXEC SP_DEL_STG_TAB 'STG_UNSUCCESS_LOGINS', '7/3/2004'
it gives an wiered error
Line 1: Incorrect syntax near '3'.
please help me on this as early as possible i am stuck at this point.
thanks in advance
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi
Use YYYYMMDD format for dates.
"sushma B V" <bv.sushma@.iflexsolutions.com> wrote in message
news:%23msW6xBFFHA.624@.TK2MSFTNGP15.phx.gbl...
>
> Hi all,
> i have SP where i need to pass the tablename and the date as the
> parameter
> i started like this
> CREATE PROC SP_DEL_STG_TAB
> @.TABLE_NAME varchar(50) ,
> @.PRESENT_REC_LD_DT DATETIME
> AS
> EXEC ( 'DELETE FROM '+ @.TABLE_NAME +' WHERE LOAD_DT =' +
> @.PRESENT_REC_LD_DT)
> GO
> when i complied i did not find error but during the execution by passing
> the parameters using
> EXEC SP_DEL_STG_TAB 'STG_UNSUCCESS_LOGINS', '7/3/2004'
> it gives an wiered error
> Line 1: Incorrect syntax near '3'.
> please help me on this as early as possible i am stuck at this point.
> thanks in advance
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Try to use a string variable and build the query, then execute it using exec
command
Babu M K
"sushma B V" <bv.sushma@.iflexsolutions.com> wrote in message
news:%23msW6xBFFHA.624@.TK2MSFTNGP15.phx.gbl...
>
> Hi all,
> i have SP where i need to pass the tablename and the date as the
> parameter
> i started like this
> CREATE PROC SP_DEL_STG_TAB
> @.TABLE_NAME varchar(50) ,
> @.PRESENT_REC_LD_DT DATETIME
> AS
> EXEC ( 'DELETE FROM '+ @.TABLE_NAME +' WHERE LOAD_DT =' +
> @.PRESENT_REC_LD_DT)
> GO
> when i complied i did not find error but during the execution by passing
> the parameters using
> EXEC SP_DEL_STG_TAB 'STG_UNSUCCESS_LOGINS', '7/3/2004'
> it gives an wiered error
> Line 1: Incorrect syntax near '3'.
> please help me on this as early as possible i am stuck at this point.
> thanks in advance
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

passing table name and order by parameter to stored procedure

can i pass the name of the table and the "order by" column name to stored procedure?

i tried the simple way

(@.tablename varchar and then "select * from @.tablename)

but i get error massesges. the same for order by...

what is the right syntex for this task?

You have to use dynamic SQL for this task, unfortunately. You have to create a character string in your sproc that puts together the SQL statement and then you have to call exec(@.sql) on it.

Example:

@.sql = 'SELECT * FROM ' + @.tableName

exec(@.sql)

|||

well, i just started to use stored procedure, i think that i will leave dynamic sql to later on...