how to convert to specific value when input is in binary string format

Question

I have a situation, where i will get a string like days varchar(7), days='1001101' I want to produce an output like 1:sunday 0:absent 0:absent 1:wensday 1:thursday 0:abset 1:saturday

final output:sunday wensday thursday saturday How can I achieve it by using T-SQL function. (Function with input output parameter)

Use @Pradeep answer since function will simply do the job for you, else have a look at the code using CASE stmt. — knkarthick24
– knkarthick24, Commented Nov 11, 2014 at 9:50

Pரதீப் · Accepted Answer · 2014-11-11 09:41:18Z

2

Use CHOOSE function to get the result. Try this..

Note : This will work only in SQL SERVER 2012+.. If you want to use it on earlier version just use Case statements instead of Choose. Just to simply the code i have used CHOOSE function

CREATE FUNCTION dbo.Func (@dayss VARCHAR(1000))
returns VARCHAR(1000)
  BEGIN
      DECLARE @cnt    INT =1,
              @outpt  VARCHAR(100),
              @fina   VARCHAR(100)='',
              @outpt1 VARCHAR(100)=''

      WHILE @cnt <= Len(@dayss)
        BEGIN
            SET @outpt = Substring(@dayss, @cnt, 1)

            SELECT @outpt1 = CASE
                               WHEN @outpt = 1 THEN @outpt
                                                    + Choose(@cnt, ':sunday ', ':monday ', ':tuesday ', ':wednesday ', ':thursday ', ':friday ', ':saturday ')
                               ELSE @outpt + ':absent '
                             END

            SET @fina += @outpt1
            SET @cnt+=1
        END

      RETURN @fina
  END

SELECT dbo.Func('1001101')

OUTPUT : 1:sunday 0:absent 0:absent 1:wednesday 1:thursday 0:absent 1:saturday

To get the final OUTPUT

CREATE FUNCTION dbo.Funcfinal (@dayss VARCHAR(1000))
returns VARCHAR(1000)
  BEGIN
      DECLARE @cnt    INT =1,
              @outpt  VARCHAR(100),
              @fina   VARCHAR(100)='',
              @outpt1 VARCHAR(100)=''

      WHILE @cnt <= Len(@dayss)
        BEGIN
            SET @outpt = Substring(@dayss, @cnt, 1)

            SELECT @outpt1 = CASE
                               WHEN @outpt = 1 THEN Choose(@cnt, 'sunday ', 'monday ', 'tuesday ', 'wednesday ', 'thursday ', 'friday ', 'saturday ')
                               ELSE ''
                             END

            SET @fina += @outpt1
            SET @cnt+=1
        END

      RETURN @fina
  END

SELECT dbo.Funcfinal('1001101')

OUTPUT : sunday wednesday thursday saturday

edited Nov 11, 2014 at 9:41

answered Nov 11, 2014 at 9:21

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nishant Over a year ago

choose?? i am using 2012 version. but it is giving following error message. Msg 195, Level 15, State 10, Procedure daysconvert, Line 17 'Choose' is not a recognized built-in function name.

Pரதீப் Over a year ago

@nishantnandgaonkar May be compatibility mode is set to SQL SERVER 2008. Check this link msdn.microsoft.com/en-IN/library/hh213019.aspx

knkarthick24 · Accepted Answer · 2014-11-11 09:49:38Z

Traditional way of doing using CASE.

WHILE ( @i <= Len(@input) )
  BEGIN
      SET @temp = Substring(@input, @i, 1)
      SET @output += ' '+(SELECT CASE
                               WHEN @temp = 1
                                    AND @i = 1 THEN 'Sunday'
                               WHEN @temp = 1
                                    AND @i = 2 THEN 'Monday'
                               WHEN @temp = 1
                                    AND @i = 3 THEN 'Tuesday'
                               WHEN @temp = 1
                                    AND @i = 4 THEN 'Wednesday'
                               WHEN @temp = 1
                                    AND @i = 5 THEN 'Thursday'
                               WHEN @temp = 1
                                    AND @i = 6 THEN 'Friday'
                               WHEN @temp = 1
                                    AND @i = 7 THEN 'Saturday'
                               ELSE ''
                             END)
      SET @i = @i + 1

  END

PRINT @output

nishant · Accepted Answer · 2014-11-11 13:54:00Z

create function dateconvert(@input varchar(100))
returns varchar(1000)
begin
declare 
@i int=1,
@temp varchar(1000)='',
@output1 varchar(1000)='',
@output varchar(1000)
while (@i<=len(@input))
    begin
        set @temp = substring(@input, @i, 1)
        set @output= ( select  case
                               when @temp = '1'
                                    and @i = 1 then 'sunday'
                               when @temp = '1'
                                    and @i = 2 then 'monday'
                               when @temp = '1'
                                    and @i = 3 then 'tuesday'
                               when @temp = '1'
                                    and @i = 4 then 'wednesday'
                               when @temp = '1'
                                    and @i = 5 then 'thursday'
                               when @temp = '1'
                                    and @i = 6 then 'friday'
                               when @temp = '1'
                                    and @i = 7 then 'saturday'
                               else '_'
                             end )
            set @output1=@output1+@output
            set @i = @i + 1 
    end
    return @output1
end

Collectives™ on Stack Overflow

how to convert to specific value when input is in binary string format

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related