Link to home
Start Free TrialLog in
Avatar of mrduckers
mrduckers

asked on

SQL: replace 'on' with 'yes' in a field in a table

Just after an SQL statement to replace 'on' with 'yes' in a field in a table

Thanks.
Avatar of nicolasdiogo
nicolasdiogo
Flag of United Kingdom of Great Britain and Northern Ireland image

is no your last word, or do you need to find it in the string?


Avatar of mrduckers
mrduckers

ASKER

the on appears because if comes from a check box in the form on the web page.

Its needs to be 'Yes' instead of 'on' so I have to update all historical records.
try

select
replace( yourColumn , ' no ' , ' yes ' )

from yourTable
ASKER CERTIFIED SOLUTION
Avatar of nicolasdiogo
nicolasdiogo
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
if it works then create an update staatement with the above function to solve your data.
REMEMBER: back up your data before hand
if it is only ONE in the middle then:

select
yourColumn , -- just to see original values
case when patIndex( '% [Nn][Oo] %' , yourColumn )  > 0 -- the string was found, ( No , NO , nO , no )
then
      left( yourColumn , patIndex( '% [Nn][Oo] %' , yourColumn ) )
      +
      'Nicolaus '
      +
      right( yourColumn ,len( yourColumn ) - patIndex( '% [Nn][Oo] %' , yourColumn ) - 6 )
else
yourColumn
end

yoursCorrected

from
yourTable
it is not  -6 but  -3
SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
or  more appropriately


UPDATE urTable
SET urOnColumn = REPLACE(urOnColumn,'On','Yes')
WHERE urOnColumn = 'On'
UPDATE    tbl
SET              field= 'yes'
WHERE     (field= 'on')

above works..

how do i replace a  <NULL> with 'Unknown' though ?
>how do i replace a  <NULL> with 'Unknown' though ?


UPDATE    tbl
SET              field= 'unknown'
WHERE     (field IS NULL)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
hi,

considering the question and level of anwsers offered here, it would seems that the asker has to choose one.

regards,

Nicolas

www.brainpowered.net