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.
Thanks.
is no your last word, or do you need to find it in the string?
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.
Its needs to be 'Yes' instead of 'on' so I have to update all historical records.
try
select
replace( yourColumn , ' no ' , ' yes ' )
from yourTable
select
replace( yourColumn , ' no ' , ' yes ' )
from yourTable
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if it works then create an update staatement with the above function to solve your data.
REMEMBER: back up your data before hand
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or more appropriately
UPDATE urTable
SET urOnColumn = REPLACE(urOnColumn,'On','Y es')
WHERE urOnColumn = 'On'
UPDATE urTable
SET urOnColumn = REPLACE(urOnColumn,'On','Y
WHERE urOnColumn = 'On'
ASKER
UPDATE tbl
SET field= 'yes'
WHERE (field= 'on')
above works..
how do i replace a <NULL> with 'Unknown' though ?
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)
UPDATE tbl
SET field= 'unknown'
WHERE (field IS NULL)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
considering the question and level of anwsers offered here, it would seems that the asker has to choose one.
regards,
Nicolas
www.brainpowered.net