Query Help - Replace multiple values without looping

  • Hi,

    I need one query help let me explain my issue

    create table #task(TaskId bigint unique, Name varchar(2000))

    insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')

    insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')

    -- select * from #task

    create table #taskxref(id bigint unique, TaskID bigint, DataElement varchar(500), Result varchar(1000))

    insert into #taskxref values(1, 1, '<<Name>>', 'ABC')

    insert into #taskxref values(2, 1, '<<Salary>>', '500')

    insert into #taskxref values(3, 2, '<<Name>>', 'XYZ')

    insert into #taskxref values(3, 2, '<<Company>>', 'Test Inc.')

    insert into #taskxref values(3, 2, '<<Salary>>', '300')

    -- select * from #taskxref

    now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name

    select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')

    I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.

    create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)

    Return table

    as

    return

    (

    with data as

    (

    select TaskID, DataElement, Result from #taskxref where TaskId = @TaskId

    )

    select TaskId, REPLACE(@Name, DataElement, ISNULL(Result,'')) TaskName

    from data

    )

    drop table #task

    drop table #taskxref

    drop function fn_TaskResolver

    Thanks

  • Nope, sorry. I have absolutely no idea what you are trying to accomplish.

  • You could use one update statement per "dataelement".

    edit: as usual I could be completely wrong!

    create table #task(TaskId bigint unique, Name varchar(2000))

    insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')

    insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')

    -- select * from #task

    create table #taskxref(id bigint, TaskID bigint, DataElement varchar(500), Result varchar(1000))

    insert into #taskxref values(1, 1, '<<Name>>', 'ABC')

    insert into #taskxref values(2, 1, '<<Salary>>', '500')

    insert into #taskxref values(3, 2, '<<Name>>', 'XYZ')

    insert into #taskxref values(3, 2, '<<Company>>', 'Test Inc.')

    insert into #taskxref values(3, 2, '<<Salary>>', '300')

    -- select * from #taskxref

    update #task

    set [name] = replace([name],b.[dataelement],b.[result])

    from #taskxref b

    where #task.taskid = b.taskid

    and b.dataelement = '<<Name>>'

    update #task

    set [name] = replace([name],b.[dataelement],b.[result])

    from #taskxref b

    where #task.taskid = b.taskid

    and b.dataelement = '<<Company>>'

    update #task

    set [name] = replace([name],b.[dataelement],b.[result])

    from #taskxref b

    where #task.taskid = b.taskid

    and b.dataelement = '<<Salary>>'

    select * from #task

  • inayatkhan (7/13/2015)


    Hi,

    I need one query help let me explain my issue

    create table #task(TaskId bigint unique, Name varchar(2000))

    insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')

    insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')

    -- select * from #task

    create table #taskxref(id bigint unique, TaskID bigint, DataElement varchar(500), Result varchar(1000))

    insert into #taskxref values(1, 1, '<<Name>>', 'ABC')

    insert into #taskxref values(2, 1, '<<Salary>>', '500')

    insert into #taskxref values(3, 2, '<<Name>>', 'XYZ')

    insert into #taskxref values(3, 2, '<<Company>>', 'Test Inc.')

    insert into #taskxref values(3, 2, '<<Salary>>', '300')

    -- select * from #taskxref

    now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name

    select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')

    I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.

    create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)

    Return table

    as

    return

    (

    with data as

    (

    select TaskID, DataElement, Result from #taskxref where TaskId = @TaskId

    )

    select TaskId, REPLACE(@Name, DataElement, ISNULL(Result,'')) TaskName

    from data

    )

    drop table #task

    drop table #taskxref

    drop function fn_TaskResolver

    Thanks

    The problem, as I understand it, is that you want a function that's INLINE, to recursively REPLACE the text values found in the Name field with the replacement values supplied in a table. If only one replacement were necessary, this would be a piece of cake, but I don't think an INLINE function is going to be able to do it. I don't quite see how I could construct a recursive CTE that could do it. If the number of possible changes is high, then even if I do manage to figure it out, it's going to probably have rather bad performance. I'll take another pass at this after lunch....

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Lol, kind of as an exercise I did do a recursive function for this task. As you said, I'm not sure about how it will perform, and I have not made any attempt yet to tune it.

    CREATE FUNCTION fn_TaskResolver (

    @TaskId bigint,

    @name varchar(2000)

    )

    RETURNS TABLE

    AS

    RETURN (

    WITH cte_taskxrefs AS (

    SELECT DataElement, Result, ROW_NUMBER() OVER(ORDER BY id) AS xref#

    FROM taskxref

    WHERE TaskId = @TaskId

    ),

    cte_replace AS (

    SELECT REPLACE(@name, DataElement, Result) Name, 1 AS replace#

    FROM cte_taskxrefs

    WHERE xref# = 1

    UNION ALL

    SELECT REPLACE(Name, DataElement, Result), replace# + 1

    FROM cte_replace cr

    INNER JOIN cte_taskxrefs ctx ON ctx.xref# = replace# + 1

    )

    SELECT Name

    FROM cte_replace

    WHERE

    replace# = (SELECT MAX(xref#) FROM cte_taskxrefs)

    )

    GO --end of function

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Will you really have that amount of data elements? You could simply code for each possible data element. It would be even better if you change the EAV design into a properly normalized table to maintain less rows.

    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
  • Scott,

    Nice work. Don't have any idea how it will perform, but here's a prettified and full set script for the entire setup, including dropping the created objects, so that others wishing to test can easily dispose of what gets created:

    CREATE TABLE dbo.taskxref (

    id bigint unique,

    TaskID bigint,

    DataElement varchar(500),

    Result varchar(1000)

    )

    insert into dbo.taskxref values(1, 1, '<<Name>>', 'ABC')

    insert into dbo.taskxref values(2, 1, '<<Salary>>', '500')

    insert into dbo.taskxref values(3, 2, '<<Name>>', 'XYZ')

    insert into dbo.taskxref values(4, 2, '<<Company>>', 'Test Inc.')

    insert into dbo.taskxref values(5, 2, '<<Salary>>', '300')

    select * from dbo.taskxref

    GO

    CREATE FUNCTION dbo.fn_TaskResolver (

    @TaskId bigint,

    @name varchar(2000)

    )

    RETURNS TABLE

    AS

    RETURN (

    WITH cte_taskxrefs AS (

    SELECT DataElement, Result, ROW_NUMBER() OVER(ORDER BY id) AS xref_num

    FROM dbo.taskxref

    WHERE TaskId = @TaskId

    ),

    cte_replace AS (

    SELECT REPLACE(@name, DataElement, Result) AS [Name], 1 AS replace_num

    FROM cte_taskxrefs

    WHERE xref_num = 1

    UNION ALL

    SELECT REPLACE([Name], DataElement, Result), replace_num + 1

    FROM cte_replace cr

    INNER JOIN cte_taskxrefs ctx

    ON ctx.xref_num = replace_num + 1

    )

    SELECT [Name] AS Changed_Name

    FROM cte_replace

    WHERE replace_num = (SELECT MAX(xref_num) FROM cte_taskxrefs)

    )

    GO

    DECLARE @task AS TABLE (

    TaskId bigint unique,

    Name varchar(2000)

    )

    insert into @task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')

    insert into @task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')

    select * from @task

    SELECT T.TaskId, X.Changed_Name

    FROM @task AS T

    CROSS APPLY dbo.fn_TaskResolver(T.TaskId, T.Name) AS X

    DROP TABLE dbo.taskxref

    GO

    DROP FUNCTION dbo.fn_TaskResolver

    GO

    I'm attaching the estimated (EDIT: and actual) execution plans, derived after creating the xref table and the function.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Thanks I hope its work in my condition.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply