0

I have a table where I need to insert a '*' sign before a lot of names. My table looks like this now:

id,name

1,cake
2,car
3,mountain

And it should be:

id,name

1,*cake
2,car
3,*mountain

The name column is nvarchar. I have the ids in a csv file. I thought I could make the update statement with concat and a where clause with the IDs, but I can't get it to work.

I tried

UPDATE table
SET    NAME = concat('*', NAME)
WHERE  id = '1'
        OR id = '2' 

But got

Msg 195, Level 15, State 10, Line 11 'concat' is not a recognized built-in function name

  • Get the IDs into a temporary table tempIDs(id), use then UPDATE my_table SET name=concat('*', name) WHERE id IN (SELECT id FROM tempIDs) – joanolo Jan 30 '17 at 7:51
  • update table set name = concat('*',name) where id = '1' or id = '2' Msg 195, Level 15, State 10, Line 11 'concat' is not a recognized built-in function name. – mgr33n Jan 30 '17 at 8:17
  • Its and MSSQL 2005 instance :( – mgr33n Jan 30 '17 at 8:19
  • As @joanolo suggest import whole csv file into a temporary table, and then update the related records. – McNets Jan 30 '17 at 8:22
  • Thanks, I got it to work. Used the + sign in instead of the concat function. – mgr33n Jan 30 '17 at 9:12
1
Begin Tran
    Create Table #TEMP(id INT,name NVARCHAR(50))

        INSERT INTO #TEMP
        SELECT 1,'cake' UNION ALL
        SELECT 2,'car' UNION ALL
        SELECT 3,'mountain'

        UPDATE #TEMP
        SET    NAME = +'*'+ NAME
        WHERE  id = '1' OR id = '2' 

        Select * from #TEMP

ROLLBACK TRAN
  • CONCAT Is Not A Build Function for SQL SERVER 2005 & 2008 But It is Available in Updated Version Of SQL Server like 2012,2014,2016 – Alfaiz Ahmed Jan 31 '17 at 6:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.