Hex to Decimal conversions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Hex to Decimal conversions

Author

Topic

Bunce
Starting Member

13 Posts

Posted - 2003-03-19 : 01:24:13

Hi all,

I'm pulling some data from an LDAP server (Active Directory) of which one attribute (UserAccountControl) is a hex number. One part of this hex number indicates whether that account is disabled or not.

Now, when pulling the data through LDAP it is returned as an integer. I need to decide if that integer represents whether the user account is disabled or not.

An example, I retrieve the following two integers and convert them to hex (VarBinary):
512 -> 0x00200 (account is enabled)
514 -> 0x00202 (account is disabled)

So basically the last (hex) digit indicates whether the user is enabled or disabled. (0-enabled, 2-disabled).

I have no troubles converting the initial integer to hex format (CAST to varbinary) however I can't seem to grab the last character (using SUBSTRING OR RIGHT) and then convert it a character so as to do a comparison.

Basically I wish to do a CASE on the last digit to return the string 'ENABLED' or 'DISABLED'

Any ideas??

Cheers,
Andrew

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-19 : 02:15:47

Try this:
declare @str varchar(10)
set @str='0x00200'
select right(@str,1) String, case right(@str,1) when 0 then 'Enabled' when 2 then 'Disabled' else 'NotDefined' end Status
set @str='0x00202'
select right(@str,1) String, case right(@str,1) when 0 then 'Enabled' when 2 then 'Disabled' else 'NotDefined' end Status

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-03-19 : 04:25:46

If UserAccountControl is an integer:


CASE UserAccountControl & 2
  WHEN 0 THEN 'Enabled'
  WHEN 2 THEN 'Disabled'
END

Edit: oops! Not enough coffee error in line 1.

Like this:


SELECT UserAccountControl,
  CASE UserAccountControl & 2
    WHEN 0 THEN 'Enabled'
    WHEN 2 THEN 'Disabled'
  END
FROM (
    SELECT CAST(0x0202 AS int) AS UserAccountControl
    UNION SELECT 0x0200
  ) AS A

Edited by - Arnold Fribble on 03/19/2003 05:37:44

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-19 : 05:02:11

quote:

AND 2

Incorrect syntax near the keyword 'AND'.
Iam getting error on this syntax.

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

Tanjung
Starting Member

1 Post

Posted - 2003-03-21 : 04:33:18

if UserAccountControl is a integer, you can use this code :

select
case
UserAccountControl % 16
when 0 then 'Disabled'
when 2 then 'Enabled'
end

-------
Tanjung
-------

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-03-21 : 05:58:34

That would be a poor idea, since the bottom 4 bits of userAccountControl contains 3 flags:

ADS_UF_SCRIPT 0x0001
The logon script is executed. This flag does not work for the ADSI LDAP provider on either read or write operations. For the ADSI WinNT provider, this flag is read only data, and it cannot be set on user objects.
ADS_UF_ACCOUNTDISABLE 0x0002
The user account is disabled.
ADS_UF_HOMEDIR_REQUIRED 0x0008
The home directory is required.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-21 : 08:54:20

Please use Arnold's method with the & operator -- that is the correct way to test whether or not bits are set in an integer field.

I also do not recommend the % operator.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources