asked on

Update SQL: Need to add conditions

I have a field called 'Accessrights', it holds a string of 11 digits, being 1 or 0.
I have the script below, which takes the first digit of the string and copies it to another field, then the second and so on. It all works great. Problem is that some of the strings have numbers '2' or they are null or the string is too short (Issues from data import).

I need to add some code to the sql so that if the digit selected is NOT 1 or 0 then to make it a 0 , then I can copy the value to the appropriate field.

Help is appreciated. Here is the current SQL:

UPDATE  [dbo].[UserLogin]
SET     [client_personalprofile] = SUBSTRING([AccessRights], 1, 1) ,
        [client_case_geninfo] = SUBSTRING([AccessRights], 2, 1) ,
        [client_case_atty] = SUBSTRING([AccessRights], 3, 1) ,
        [client_case_comments] = SUBSTRING([AccessRights], 5, 1) ,
        [client_case_docs] = SUBSTRING([AccessRights], 6, 1) ,
        [client_case_billing] = SUBSTRING([AccessRights], 7, 1) ,
        [client_case_steps] = SUBSTRING([AccessRights], 8, 1) ,
        [client_relcases] = SUBSTRING([AccessRights], 9, 1) ,
        [client_maincase] = SUBSTRING([AccessRights], 10, 1) ,
        [client_case_forms] = SUBSTRING([AccessRights], 11, 1);

Open in new window

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aleks

ASKER

I did a select to test and they show exactly the same values:

        
                  SELECT    CASE WHEN SUBSTRING([AccessRights], 9, 1) NOT IN (
                                      0, 1 ) THEN 0
                                 ELSE SUBSTRING([AccessRights], 1, 1)
                            END AS client_profilemodified ,
                            SUBSTRING([AccessRights], 1, 1) AS [client_personalprofile]
                  FROM      dbo.UserLogin

Open in new window

It still shows null values, numbers 2, etc.

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aleks

ASKER

I apologize, you are right. The only thing missing is that if the field is null I also need to set it to '0'

SOLUTION