none
Convert data type to text for case statement RRS feed

  • Question

  • I need to convert data type to text for following statement. Can someone please help…

    ,case

    when [Order_ID] is null then 'N'

    when [Order_ID] = '' then 'N'

    else [Order_ID]

    end as [Order_ID]

    Many thanks


    Wednesday, October 2, 2019 3:03 AM

Answers

  • Hi Saphire77,

    To Varchar(10).

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Saphire77 Friday, October 4, 2019 2:00 AM
    Friday, October 4, 2019 1:42 AM

All replies

  • create table #t (orderid int)

    insert into #t values (100),(200),(NULL),(300)

    select case
    when cast(orderid as varchar(10)) is null then 'N'
    when cast(orderid as varchar(10))  = '' then 'N'
    else cast(orderid as varchar(10)) 
    end as orderid
    from #t

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 2, 2019 4:54 AM
  • Hi Saphire77,

    If your data type is int, you don't have to judge whether [Order_ID] = ' ', it will insert 0 automatically.

    Please try the following codes.

    create table #t (orderid int)

    insert into #t values (100),(200),(NULL),(300),('') select * from #t select case when orderid is null then 'N' else cast(orderid as varchar(10)) end as orderid from #t

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Wednesday, October 2, 2019 6:01 AM
  • Thank you Zoe , one more quick question… My data flow is as follows:

    Excel Template (Data Type is formatted to Number) à Loading Table (Particular column formatted to data type: Nvarchar(10) à Table (Particular column formatted to data type: Nvarchar(10)

    Template Data field contains only "Y" or "N" but by mistake this field has formatted to "Number" now we won't be able to do any changes to the template. My concern is if users put any numbers or any other characters SQL table should not accept it so that's why I want to convert data type to "Text".

    In your answer its convert to varchar(10), can you please advise me whether I should convert it to a "Text" field or "Varchar(10)?

    Thank you so much.


    Wednesday, October 2, 2019 9:12 PM
  • Thank you Uri , one more quick question… My data flow is as follows:

    Excel Template (Data Type is formatted to Number) à Loading Table (Particular column formatted to data type: Nvarchar(10) à Table (Particular column formatted to data type: Nvarchar(10)

    Template Data field contains only "Y" or "N" but by mistake this field has formatted to "Number" now we won't be able to do any changes to the template. My concern is if users put any numbers or any other characters SQL table should not accept it so that's why I want to convert data type to "Text".

    In your answer its convert to varchar(10), can you please advise me whether I should convert it to a "Text" field or "Varchar(10)?

    Thank you so much.


    Wednesday, October 2, 2019 9:12 PM
  • Hi Saphire77,

    To Varchar(10).

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Saphire77 Friday, October 4, 2019 2:00 AM
    Friday, October 4, 2019 1:42 AM
  • Thanks Zoe, appreciate all your help...
    Friday, October 4, 2019 2:01 AM