none
Quicker Conversions to Bit (boolean) type RRS feed

  • Question

  • Okay,

    Simply put I have some "Columnar" data being imported and was wondering if there was a better method for converting a string type into a BIT.  It is always assured that when comparing numbers (actual numbers, not string representations of numbers) it is faster than comparing strings.  strings has to load up the comparison for each character, etc, etc, etc. 

    I have some fields that Simply contain a 'Yes', 'no' value and would like to convert them to a bit so in my future SQL statements with the resulting data, I can simply do:  WHERE X = 1, as well as save soem of the storage space during the import/conversion process.

    Currently the only method I have found is:

    CASE [MyCol] WHEN 'YES' THEN 1 ELSE 0 END

    Is there a better way, faster way, simpler way...yadda, yadda, yadda. 

    Thansk

    Jaeden "Sifo Dyas" al'Raec Ruiner


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Monday, March 28, 2011 4:43 AM

Answers

  • No, there ist no conversion from string to bit like "CONVERT(bit, [MyVal]='Yes')"

    If you have different values in yource like "true", "yes","x" I suggest to create a translationtable

    create table tbl_translate_bit (org_val varchar(50), dest_val bit)

    Write all possible value there with the conversion to bit  and join the table while inserting your data 

    • Marked as answer by JaedenRuiner Monday, March 28, 2011 8:15 AM
    Monday, March 28, 2011 6:36 AM

All replies

  • Hi,

    try this

    create  table #temp(s varchar(5))
    insert into #temp
    select 'yes'
    union all
    select 'no'

    update #temp set s=case when s='yes' then 1 else 0 end

    select * from #temp
    alter table #temp alter column s bit


    Thanks and regards, Rishabh
    Monday, March 28, 2011 5:01 AM
  • Ahh,

    yea, i intend to perform this process as part of a full insert into statement.  the data is originating from an excel file so I'll be using adhoc querying and basically running a CONVERT() statement on each column i'm pulling to control data meaning, etc, etc.

    SO Simply put:

    SELECT CONVERT(bit, CASE [MyVal] WHEN 'yes' THEN 1 ELSE 0 END)

    when combined with 19 other columns being "converted" i was just wondering if ther was a slick way of making that statement more like this:

    SELECT CONVERT(bit, [MyVal]='Yes')

    But I guess not.  :P

    Actually, since the "actual" column data is either 'X', or '', I thought of this:

    CONVERT(bit, ISNULL(ASCII([MyVal]),87) - 87)

    Thanks

    J"SD"a'RR

     


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Monday, March 28, 2011 5:10 AM
  • No, there ist no conversion from string to bit like "CONVERT(bit, [MyVal]='Yes')"

    If you have different values in yource like "true", "yes","x" I suggest to create a translationtable

    create table tbl_translate_bit (org_val varchar(50), dest_val bit)

    Write all possible value there with the conversion to bit  and join the table while inserting your data 

    • Marked as answer by JaedenRuiner Monday, March 28, 2011 8:15 AM
    Monday, March 28, 2011 6:36 AM
  • Actually, since the "actual" column data is either 'X', or ''

    Assuming X means yes.

    SELECT CAST(LEN([MyVal]) AS BIT)
    FROM t
    

     


    Jon
    Monday, March 28, 2011 8:53 AM