maloriopolium
asked on
How to update a value in a column
Hi,
I have a table called Schedule and I would like to update a column called MyColumn. I want all strings that start with ABCD to be replaced by ZZZZ. I am using MS SQL Server. Can anybody help me do this?
Thanks
I have a table called Schedule and I would like to update a column called MyColumn. I want all strings that start with ABCD to be replaced by ZZZZ. I am using MS SQL Server. Can anybody help me do this?
Thanks
Update Schedule
Set MyColumn = 'ZZZZ' + SubString(MyColumn , 5, datalength(MyColumn)-4)
where MyColumn like 'ABCD%'
Set MyColumn = 'ZZZZ' + SubString(MyColumn , 5, datalength(MyColumn)-4)
where MyColumn like 'ABCD%'
ignore my comment, I thought you want to replace the part ABCD with ZZZZ if the value starts with ABCD.
ASKER
Sorry, what I meant was I want to keep the stuff after ABCD. For example: ABCD12345 becomes ZZZZ12345.
Thanks
Thanks
UPDATE Schedule SET MyColumn =REPLACE(MyColumn, 'ABCD', 'ZZZZ')
WHERE MyColumn LIKE 'ABCD%'
WHERE MyColumn LIKE 'ABCD%'
ASKER
Thanks howyue,
I have a problem. The column called MyColumn is actually called File
I get an error saying argument 1 of Replace function is not valid text? Any ideas
I have a problem. The column called MyColumn is actually called File
I get an error saying argument 1 of Replace function is not valid text? Any ideas
what is the datatype of ur [File] ?
UPDATE Schedule SET File =REPLACE(File , 'ABCD', 'ZZZZ')
WHERE File LIKE 'ABCD%'
UPDATE Schedule SET File =REPLACE(File , 'ABCD', 'ZZZZ')
WHERE File LIKE 'ABCD%'
ASKER
The dat type is just text
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ur column is text, ok thats where the problem come from, REPLACE does not accept text as argument, however u can:
UPDATE Schedule SET [File] = REPLACE(CONVERT(VARCHAR(MAX), [File]), 'ABCD', 'ZZZZ')
WHERE Remarks LIKE 'ABCD%'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, what I ended up doing was changing the datatype from text to varchar. Now it works.
Just out of curiosity. What is the difference between varchar and text and which should be used when?
Thanks
Just out of curiosity. What is the difference between varchar and text and which should be used when?
Thanks
the essential different is that the maximum of their storage size. beside that, i'm not so sure. but in BOL microsoft suggests:
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.
and you better avoid replace, if you want to replace only the first occurance of ABCD.
replace will will replace all instances of ABCD in your text.
suppose your column has value "ABCDTest ABCD replace"
replace(yourCol,'ABCD', 'ZZZZ') will result "ZZZZTest ZZZZ replace"
replace will will replace all instances of ABCD in your text.
suppose your column has value "ABCDTest ABCD replace"
replace(yourCol,'ABCD', 'ZZZZ') will result "ZZZZTest ZZZZ replace"
WHERE MyColumn LIKE 'ABCD%'