sql prepend entries
I have some entries that are inconstant, they should be prepended by the same string.
some have numbers and other have the 开发者_Go百科dollar sign and the number
so I have a syntax that finds all the entries which do not have the dollar sign
WHERE [mydata] not like '$%'
how do I add the string before each entry?
update table set mydata = '$' + mydata where [mydata] not like '$%'
The + only works in SQLServer; you may need to use the concatenate
function otherwise.
If you are looking for just a select statement to return the value with a $, this will work:
Select '$' + field
from [table]
where field not like '$%'
You can run it as a case statement had you wanted both records that have a $ and those without to be returned with the $
Select case when left(field,1) = '$' then '' else '$' end + field
from [table]
where field not like '$%'
edit: Might need to convert the 'field' into a varchar for the + to work, you'll get a syntax error if the field is an int (but you have the $ spuradically in the field, so I assumed it's a varchar
精彩评论