Match Values UP - SQL Server Forums

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

Transact-SQL (2008)

Match Values UP

Author

Topic

PBoy
Starting Member

22 Posts

Posted - 2015-01-12 : 13:58:46

HI All,

Just wondering if there is a way of matching up values based on there location in a string and replace on a column value?

For example I have the string in a column with the value "dim_value_3,GN,,GN"

Would like to map the values from above to the below string based on its location.

Routing error. Recipient type: From column. Connection between column %s and recipient %s. Value to be matched is %s. No value found for attribute %s.

So every value replaces the %s depending in what order it is found so the result would look like below.

Routing error. Recipient type: From column. Connection between column dim_value_3 and recipient GN. Value to be matched is _ . No value found for attribute GN.

its would have to find the values dynamically as some of the data changes like below

dim_value_3,GN,,GN
BUSUNIT,health_safety,C1DEP2

Any help would be great.

Cheers
P

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-12 : 14:00:16

You could use the PARSENAME function to pick apart the individual values. Then build it into the string by concatenating it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-12 : 14:01:29

I should add that the PARSENAME function works when "." is the delimiter. If you won't have "." in there, then do a quick REPLACE on it to switch from "," to ".". PARSENAME works when there are at most 4 parts to it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-12 : 15:59:58

Is the message pattern:
"
Routing error. Recipient type: From column. Connection between column dim_value_3 and recipient GN. Value to be matched is _ . No value found for attribute GN.
"
static for each set of data to be processed? More specifically, can the msg pattern be "pre-processed" to make the replacement easier?

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-12 : 17:11:36

[code]
IF OBJECT_ID('tempdb.dbo.#message_patterns') IS NOT NULL
DROP TABLE #message_patterns
CREATE TABLE #message_patterns (
message_pattern varchar(5000) NOT NULL,
substitution_count int NULL
)

INSERT INTO #message_patterns ( message_pattern, substitution_count )
SELECT message, (LEN(message) - LEN(REPLACE(message, '%s', ''))) / 2
FROM (
SELECT 'Routing error. Recipient type: From column. Connection between column %s and recipient %s. Value to be matched is %s. No value found for attribute %s.' AS message
) AS messages
--SELECT * FROM #message_patterns

DECLARE @substition_number int
SET @substition_number = 1
WHILE 1 = 1
BEGIN
UPDATE #message_patterns
SET message_pattern = STUFF(message_pattern, CHARINDEX('%s', message_pattern), 2, CHAR(7) + CAST(@substition_number AS char(1)))
WHERE CHARINDEX('%s', message_pattern) > 0
IF @@ROWCOUNT = 0
BREAK
SET @substition_number = @substition_number + 1
END --WHILE
--SELECT * FROM #message_patterns

SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(message_pattern,
CHAR(7) + '1', ISNULL(value_1, '_')),
CHAR(7) + '2', ISNULL(value_2, '_')),
CHAR(7) + '3', ISNULL(value_3, '_')),
CHAR(7) + '4', ISNULL(value_4, '_')),
CHAR(7) + '5', ISNULL(value_5, '_')),
CHAR(7) + '6', ISNULL(value_6, '_')),
CHAR(7) + '7', ISNULL(value_7, '_')),
CHAR(7) + '8', ISNULL(value_8, '_')),
CHAR(7) + '9', ISNULL(value_9, '_')) AS message
FROM (
SELECT
id,
NULLIF(MAX(CASE WHEN ItemNumber = 1 THEN Item END), ' ') AS value_1,
NULLIF(MAX(CASE WHEN ItemNumber = 2 THEN Item END), ' ') AS value_2,
NULLIF(MAX(CASE WHEN ItemNumber = 3 THEN Item END), ' ') AS value_3,
NULLIF(MAX(CASE WHEN ItemNumber = 4 THEN Item END), ' ') AS value_4,
NULLIF(MAX(CASE WHEN ItemNumber = 5 THEN Item END), ' ') AS value_5,
NULLIF(MAX(CASE WHEN ItemNumber = 6 THEN Item END), ' ') AS value_6,
NULLIF(MAX(CASE WHEN ItemNumber = 7 THEN Item END), ' ') AS value_7,
NULLIF(MAX(CASE WHEN ItemNumber = 8 THEN Item END), ' ') AS value_8,
NULLIF(MAX(CASE WHEN ItemNumber = 9 THEN Item END), ' ') AS value_9
FROM (
SELECT 1 AS id, 'dim_value_3,GN,,GN' AS data UNION ALL
SELECT 2 AS id, 'BUSUNIT,health_safety,C1DEP2'
) AS test_data
CROSS APPLY dbo.DelimitedSplit8K ( data, ',' ) AS ds
GROUP BY id
) AS data
CROSS JOIN #message_patterns mp

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources