Arrays in SQL Server help me fast

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Arrays in SQL Server help me fast

Author

Topic

bhanu_9
Starting Member

1 Post

Posted - 2005-09-01 : 00:56:07

Hi All ,

I would like to create a procedure which will take an array as an
argument and the array should be first split and then it should be stored in the temp table so that when i use a select statement the
data should be displayed as

TEST
+
REST
-
WEST
*
HIGH
etc

the procedure should be like this X(Array as argument)
this array will have pattern matching characters like +,- etc
so the array will be "TEST+WEST-REST*HIGH" etc

this array has to be split stored in the table and then when the select statement is issued should print

TEST
+
REST
-
WEST
*
HIGH

Please provide me with the code

I have written the following code can any one tell me what has to be done so that i get the desired output

drop procedure Split_String_Pattern -- drops the procedure

/* Creating the procedure which will take a string as an argument
and that string is going to be split when ever we encounter a specific pattern.
The pattern symbols could be */+-

*/

Create procedure Split_String_Pattern ( @Array varchar(2000)) AS
set nocount on

SET TEXTSIZE 0

-- Create variables for the character string and for the current position in the string.

DECLARE @position int, @string VARCHAR(2000),@SSUBSTR VARCHAR(2000),@nASSCIIVAL INT,@SSTR VARCHAR(2000)

SET @SSTR=''

-- Initialize the current position and the string variables.

SET @position = 1 --Initialization of the current position variable

SET @string = @Array -- Initialization of the string variable

WHILE @position <= DATALENGTH(@string)
BEGIN

SET @SSUBSTR=SUBSTRING(@string, @position, 1)

SET @nASSCIIVAL=ASCII(@SSUBSTR)

IF (@nASSCIIVAL >=65 AND @nASSCIIVAL < 91) OR (@nASSCIIVAL >= 97 AND @nASSCIIVAL < 123) OR (@nASSCIIVAL >=48 AND @nASSCIIVAL<=57)

BEGIN

SET @SSTR=@SSTR+@SSUBSTR

END

ELSE

BEGIN

PRINT @SSTR

PRINT @SSUBSTR

select @SSTR

select @SSUBSTR

SET @SSTR=''

END

SET @position = @position + 1

END

begin transaction

insert into bhanu (out_value) values (@SSTR);
commit transaction

GO
select * from bhanu

/* Execution of the procedure is done as follows

*/

Declare @ArrayString varchar(4000)
set @ArrayString ='TEST+WEST-REST*HIGH '
exec Split_String_Pattern @ArrayString

assis me fast please

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 01:03:24

Refer split functions here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Refer Arrays in SQL Server here
http://www.sommarskog.se/arrays-in-sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources