• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

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.
0
mrduckers
Asked:
mrduckers
  • 7
  • 3
  • 2
  • +2
4 Solutions
 
nicolasdiogoCommented:
is no your last word, or do you need to find it in the string?


0
 
mrduckersAuthor Commented:
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.
0
 
nicolasdiogoCommented:
try

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

from yourTable
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
nicolasdiogoCommented:

try

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

from yourTable
0
 
nicolasdiogoCommented:
if it works then create an update staatement with the above function to solve your data.
REMEMBER: back up your data before hand
0
 
nicolasdiogoCommented:
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
0
 
nicolasdiogoCommented:
it is not  -6 but  -3
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi mrduckers,

UPDATE urTable
SET urOnColumn = REPLACE(urOnColumn,'On','Yes')



Aneesh R!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
or  more appropriately


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

above works..

how do i replace a  <NULL> with 'Unknown' though ?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>how do i replace a  <NULL> with 'Unknown' though ?


UPDATE    tbl
SET              field= 'unknown'
WHERE     (field IS NULL)
0
 
bharatbutaniCommented:
update tbl
set column = 'Unknown'
where column is null



Regards
Bharat Butani.
0
 
imran_fastCommented:
UPDATE    tbl
SET              field= isnull(replace (field, 'on', 'yes'),'Unknown')


above will work.
0
 
nicolasdiogoCommented:
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
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now