May 25, 2015 at 9:12 am
Hi Team,
using below query to raplace the string values (REPLACE abc with T1223), how to use the query without hard coding.
i want to store the values in another temp table and access in main query.
'abc', 'T1223',
'def', 'T456',
'ghi', 'T789',
'jkl', 'T1011',
'mno', 'T12'
select id,name,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(name,
'abc', 'T1223'),
'def', 'T456'),
'ghi', 'T789'),
'jkl', 'T1011'),
'mno', 'T12'))) New_id
from TAB
Any suggessions Please..
May 25, 2015 at 10:42 am
I'm not sure if this will be a good option because it might hit performance. However, it's a solution that might help.
Maybe someone else could post a better option to prevent the use of a scalar function.
CREATE TABLE dbo.ReplaceValues(
OriginalValue varchar(10),
NewValue varchar(10)
)
INSERT INTO dbo.ReplaceValues
VALUES
('abc', 'T1223'),
('def', 'T456'),
('ghi', 'T789'),
('jkl', 'T1011'),
('mno', 'T12')
GO
CREATE FUNCTION dbo.MultipleReplace
(
@String varchar(8000)
)
RETURNS varchar(8000) WITH SCHEMABINDING
AS
BEGIN
SELECT @String = REPLACE( @String, OriginalValue, NewValue)
FROM dbo.ReplaceValues
RETURN @String
END
GO
select id,
name,
LTRIM(RTRIM( dbo.MultipleReplace(name))) New_id
from (VALUES(1, 'abc def ghi jkl mno pqr stu'))TAB(id, name)
May 25, 2015 at 11:16 am
Minnu (5/25/2015)
Hi Team,using below query to raplace the string values (REPLACE abc with T1223), how to use the query without hard coding.
i want to store the values in another temp table and access in main query.
'abc', 'T1223',
'def', 'T456',
'ghi', 'T789',
'jkl', 'T1011',
'mno', 'T12'
select id,name,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(name,
'abc', 'T1223'),
'def', 'T456'),
'ghi', 'T789'),
'jkl', 'T1011'),
'mno', 'T12'))) New_id
from TAB
Any suggessions Please..
Again, your request is a bit difficult to understand because you've not provided the necessary test data or DDL. Please see the your other post which appears to be a near duplicate to this one.
http://www.sqlservercentral.com/Forums/Topic1688465-3077-1.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2015 at 11:17 am
I managed to solve it with a recursive CTE.
WARNING: performance may be suboptimal.
DECLARE @replacements TABLE (
id int,
old varchar(50),
new varchar(50)
);
INSERT INTO @replacements
VALUES
(1,'abc', 'T1223'),
(2,'def', 'T456'),
(3,'ghi', 'T789'),
(4,'jkl', 'T1011'),
(5,'mno', 'T12');
DECLARE @theStrings TABLE (
string_id int,
theString varchar(max)
);
INSERT INTO @theStrings
VALUES
(1, 'abcdef' ),
(2, 'abcdefghijklmno' ),
(3, 'ghijklmno' );
WITH rep AS (
SELECT string_id, theString AS OriginalString, REPLACE(theString, old, new) AS replacedString,
id
FROM @theStrings t
CROSS JOIN @replacements r
WHERE id = 1
UNION ALL
SELECT r.string_id, r.OriginalString, REPLACE(replacedString, old, new) AS replacedString,
rl.id
FROM @replacements rl
INNER JOIN rep AS r
ON rl.id = r.id + 1
)
SELECT OriginalString, replacedString
FROM (
SELECT OriginalString, replacedString, rn = ROW_NUMBER() OVER (PARTITION BY string_id ORDER BY id DESC)
FROM rep
) AS data
WHERE rn = 1;
-- Gianluca Sartori
May 26, 2015 at 2:36 am
Hi Team,
Above T-SQL code is exactly resolved my requirement, but i've two database tables (@replacements, @theStrings), how to use the below code with actual tables.
WITH rep AS (
SELECT string_id, theString AS OriginalString, REPLACE(theString, old, new) AS replacedString,
id
FROM @theStrings t
CROSS JOIN @replacements r
WHERE id = 1
UNION ALL
SELECT r.string_id, r.OriginalString, REPLACE(replacedString, old, new) AS replacedString,
rl.id
FROM @replacements rl
INNER JOIN rep AS r
ON rl.id = r.id + 1
)
SELECT OriginalString, replacedString
FROM (
SELECT OriginalString, replacedString, rn = ROW_NUMBER() OVER (PARTITION BY string_id ORDER BY id DESC)
FROM rep
) AS data
WHERE rn = 1;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply