CODEWITHSUNDEEP
sql
Daniel
Daniel

Reputation: 35684

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 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?

Upvotes: 1

Views: 443

Answers (2)

Twelfth
Twelfth

Reputation: 7180

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

Upvotes: 1

GendoIkari
GendoIkari

Reputation: 11914

update table set mydata = '$' + mydata where [mydata] not like '$%'

The + only works in SQLServer; you may need to use the concatenate function otherwise.

Upvotes: 4

Related Questions