Remove Characters From A Field Query

  • Hi everyone

    Some fields have a "^" in the name that need to be removed.  For example, "^ABC" needs to be "ABC".  Not sure what is wrong with my query.  Can someone please help?

    Query:

    DROP TABLE IF EXISTS #TEMP1
    DROP TABLE IF EXISTS #PROC_DATES

    CREATE TABLE #TEMP1
    (
    [SYMBOL] [NVARCHAR](10) NOT NULL,
    [TXN_DATE] [DATE] NOT NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE #PROC_DATES
    (
    [PROC_DATE] [DATE] NOT NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO #TEMP1
    VALUES ('ABC','2025-04-24'),
    ('^DEF','2025-04-25')

    INSERT INTO #PROC_DATES
    VALUES ('2025-04-25')

    SELECT *
    FROM #TEMP1

    SELECT *
    FROM #PROC_DATES

    UPDATE #TEMP1
    SET T1.SYMBOL = TRIM(REPLACE(T1.SYMBOL,'^',''))
    FROM #TEMP1 AS T1 INNER JOIN #PROC_DATES AS T2 ON T1.TXN_DATE = T2.PROC_DATE
    WHERE CHARINDEX('^', T1.SYMBOL) = 1

    SELECT *
    FROM #TEMP1

    Error:

    Msg 4104, Level 16, State 1, Line 33
    The multi-part identifier "T1.SYMBOL" could not be bound.

    Completion time: 2025-04-30T19:50:49.8107647-07:00

    Thank you

    • This topic was modified 4 months ago by water490.
  • I was playing around with the query.  I figured out the problem.  The query didn't like the alias.  Once I removed the alias then it worked.  That is so odd that SS didn't like the alias.

  • When you alias a table in an update, you don't update the table you update the alias

    You simply just needed to do

    UPDATE T1

    Instead of

    UPDATE #TEMP1

    DROP TABLE IF EXISTS #TEMP1
    DROP TABLE IF EXISTS #PROC_DATES

    CREATE TABLE #TEMP1
    (
    [SYMBOL] [NVARCHAR](10) NOT NULL,
    [TXN_DATE] [DATE] NOT NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE #PROC_DATES
    (
    [PROC_DATE] [DATE] NOT NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO #TEMP1
    VALUES ('ABC','2025-04-24'),
    ('^DEF','2025-04-25')

    INSERT INTO #PROC_DATES
    VALUES ('2025-04-25')

    SELECT *
    FROM #TEMP1

    SELECT *
    FROM #PROC_DATES

    UPDATE T1
    SET T1.SYMBOL = TRIM(REPLACE(T1.SYMBOL,'^',''))
    FROM #TEMP1 AS T1 INNER JOIN #PROC_DATES AS T2 ON T1.TXN_DATE = T2.PROC_DATE
    WHERE CHARINDEX('^', T1.SYMBOL) = 1

    SELECT *
    FROM #TEMP1

Viewing 3 posts - 1 through 3 (of 3 total)

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