Convert string 'Y' to true and 'N' to false

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Convert string 'Y' to true and 'N' to false

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-12-27 : 15:53:29

In my move of data from an old database to a new database I need to convert the Y and N entries in a char(1) column into a 1 and 0 for a bit column. I know I can do


CASE OldColName
WHEN 'Y' THEN 0
WHEN 'N' THEN 0
ELSE 0
END

but is there a more shorthand way of doing this since it will be inside an INSERT INTO SELECT statement?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-27 : 16:00:38

I assume you meant to set the 'Y' condition to 1, right?

Not really but I would do this:

case when OldColName = 'Y' then 1 else 0 end

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-28 : 03:24:37

I would also do it by means of CASE ..WHEN as shown above
But just to show an alternate way this is how it can be done otherwise


CAST(ASCII(UPPER(OldColName))-78 AS bit)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kristen
Test

22859 Posts

Posted - 2013-12-28 : 09:25:05

I would do


CASE OldColName
WHEN 'Y' THEN 1
WHEN 'N' THEN 0
ELSE -1
END

as I would want to "handle" incorrect values.

Alternative is to SELECT for them first, sort them out, and then process the data. We tend to do that, for one-off conversions, as follows:


-- Copy data to temporary table
SELECT *,
       [ErrNo] = CONVERT(int, NULL),
       [ErrMsg] = CONVERT(varchar(1000), NULL)
INTO #MyTempTable
FROM MyTable

-- Check value of OldColName is Y or N
UPDATE U
SET ErrNo = 1,
    ErrMsg = COALESCE(ErrMsg+'. ', '') + 'Illegal value for OldColName [' + OldColName + ']'
FROM #MyTempTable
WHERE     OldColName NOT IN ('Y', 'N')
      AND OldColName IS NOT NULL

... perform more "update tests" ...

-- Display errors
SELECT MyPKey1, MyPKey2, ...,
       ErrNo,
       ErrMsg
FROM #MyTempTable
WHERE ErrNo IS NOT NULL

IF @@ROWCOUNT >= 1 GOTO ABORT

... No errors, Run the actual table-insert process ...

:ABORT

Where necessary you could also add additional NewColName values to #MyTempTable so that you could process the Y / N to 1 / 0 , and then those values would be available to the INSERT routine instead of doing the conversion during the INSERT. This is particularly valuable if there are chicken-and-egg steps required in converting the old data to the new etc. or the conversion process requires multiple passes/steps.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-30 : 16:59:27

SELECT CHARINDEX(OldColName, 'NY') - 1

For example:



SELECT OldColName, CHARINDEX(OldColName, 'NY') - 1
FROM (
    SELECT 'Y' AS OLDCOLNAME UNION ALL
    SELECT 'N' UNION ALL
    SELECT ' '
) AS test_data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources