July 20, 2006

Binary To Decimal In Microsoft SQL

Filed under: Microsoft SQL — Phillip Holmes @ 12:00 am

I wrote this one to help a guy out that had posted on www.houseoffusion.com. I think it is an interesting approach to solve the problem of converting a binary octet to decimal, so I decided to post it here for posterity's sake.

PLAIN TEXT

SQL:

  1. DECLARE
  2.   @myOctet varchar(32),
  3.   @i int,
  4.   @power int,
  5.   @result int
  6. SET @myOctet = reverse('00111100')
  7. SET @power = len(@myOctet) - 1
  8. SET @i = @power + 1
  9. SET @result = 0
  10. WHILE @i>= 1
  11. BEGIN
  12.   SET @result = @result + substring(@myOctet,@i,1) * Power(2,@power)
  13.   SET @i = @i - 1
  14.   SET @power = @power - 1
  15. END
  16. SELECT @result

February 16, 2006

Search Stored Procs

Filed under: Microsoft SQL — Phillip Holmes @ 3:59 am

A short follow-up post to information_schema view. You can also use INFORMATION_SCHEMA.ROUTINES to identify / retrieve information about stored procedures.

PLAIN TEXT

SQL:

  1. SELECT *
  2. FROM INFORMATION_SCHEMA.ROUTINES
  3. WHERE ROUTINE_TYPE='PROCEDURE'

Enjoy!

January 15, 2006

INFORMATION_SCHEMA.COLUMNS

Filed under: Microsoft SQL — Phillip Holmes @ 2:59 am

When there is no documentation about your db, this little snippet can help you quickly find your way around in a pinch.

PLAIN TEXT

SQL:

  1. USE YOURDBNAME
  2.  
  3. SELECT *
  4. FROM INFORMATION_SCHEMA.COLUMNS
  5.  
  6. IF you want TO narrow your search, you can ADD the LIKE predicate AS shown:
  7.  
  8. SELECT *
  9. FROM INFORMATION_SCHEMA.COLUMNS
  10. WHERE column_name LIKE '%foo'

The Holmes Blog