June 27, 2008 at 4:41 pm
I desperately trying to find a solution to converting a varchar column to a bit column. I'm doing some data conversion between two databases and there is one table that has a varchar in the old database and needs to be converted to a bit in the new database. Can anyone assist with this endeavor?
column1-
clnt_access varchar(3) NOT NULL
needs to be converted to.....
column 2 -
clnt_access BIT, NOT NULL
June 27, 2008 at 5:27 pm
Insufficient information -- Do you mean convert a possible 3 characters (clnt_access varchar(3) NOT NULL) to binary, since a bit can only be a 1 or 0, or to binary?
(BOL data types:
ms-help://MS.SQLCC.v9/MS.SQLMobile.v3.en/SSMMain3/html/a940aac1-0bde-47e2-baa6-d12ef619ad05.htm)
June 30, 2008 at 7:47 am
Sample data from table in question(f_tablename)
clnt_access <---column varchar(3), NOT NULL
No
No
No
No
Yes
Yes
Yes
Yes
No
No
Yes
Yes
clnt_access <--column bit, not null
0
1
0
0
I am trying to convert the yes/no in the first table to 1 or 0 in the second table where 1=yes and 0=no
July 1, 2008 at 7:09 am
Try this:
DECLARE @OLD_TABLE TABLE (CLNT_ACCESS varchar(3))
INSERT INTO @OLD_TABLE
SELECT 'Yes' UNION ALL
SELECT 'No' UNION ALL
SELECT 'Yes' UNION ALL
SELECT 'Yes' UNION ALL
SELECT 'No' UNION ALL
SELECT 'Yes' UNION ALL
SELECT 'Yes' UNION ALL
SELECT 'No' UNION ALL
SELECT 'Yes' UNION ALL
SELECT 'Yes'
DECLARE @NEW_TABLE TABLE (CLNT_ACCESS BIT)
INSERT INTO @NEW_TABLE
SELECT CASE CLNT_ACCESS WHEN 'Yes' THEN 1
ELSE 0 END
FROM @OLD_TABLE
SELECT *
FROM @NEW_TABLE
Steve
(aka smunson)
:):):)
brian tompkins (6/30/2008)
Sample data from table in question(f_tablename)clnt_access <---column varchar(3), NOT NULL
No
No
No
No
Yes
Yes
Yes
Yes
No
No
Yes
Yes
clnt_access <--column bit, not null
0
1
0
0
I am trying to convert the yes/no in the first table to 1 or 0 in the second table where 1=yes and 0=no
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply