Alter query to avoid hardcoding

  • 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..

  • 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)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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