How to get the concatenated value ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

How to get the concatenated value ?

Author

Topic

bhavna
Starting Member

6 Posts

Posted - 2008-12-10 : 17:23:12

Below is my code . I am trying to parse ( 1:4:6:8,':') using the function then I get the map_name from MAPPING table . After I get map_name , I rebuild the field with map_name and :

So, it should be something like setup:mainmenu:press:userforced where 1 maps to setup , 4 maps to mainmenu , 6 maps to press , 8 maps to userforced in MAPPING table but I am getting null values for SET @newArray = @new_array + @map_name + @separator and if I put SET @newArray = @map_name + @separator, I am getting userforced:

Please help

-Bhavna

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION IVR_ADMIN.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS varchar(8000)
AS
BEGIN
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
DECLARE @separator_position INT -- This is used to locate each separator character
DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
DECLARE @newArray VARCHAR(8000)
DECLARE @map_name VARCHAR(50)

SET @array = @array + @separator

-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
-- This is where you process the values passed.
--INSERT into map_name
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
-- This replaces what we just processed with and empty string
SELECT @map_name = MAP_NAME from IVR_ADMIN.IVR_MAPPING where MAP_ID = @array_value
SET @newArray = @new_array + @map_name + @separator

SELECT @array = STUFF(@array, 1, @separator_position, '')
END

RETURN @newArray
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 17:32:27

Why not use some of the existing and tested split functions here at SQLTeam?
One of them is fnParseList. Another is fnParseString depending on your business rules.

E 12°55'05.63"
N 56°04'39.26"

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-10 : 17:36:21

SET @array = '' first

bhavna
Starting Member

6 Posts

Posted - 2008-12-10 : 17:56:39

I tried to use fnParseList but I am getting error

Server: Msg 409, Level 16, State 1, Procedure fnParseString, Line 16
The assignment operator operation cannot take a text data type as an argument.
Server: Msg 8116, Level 16, State 1, Procedure fnParseString, Line 16
Argument data type text is invalid for argument 1 of reverse function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources