Gonzo TechNet
T-SQL (Transact-SQL) Tips & Tricks
T-SQL:Select Statement to validate an IP Address
-- =========================================== -- A VALID IP Address will return 1 -- =========================================== DECLARE @IPAddress varchar(100) SELECT @IPAddress = '' SELECT @IPAddress = ltrim(rtrim(@IPAddress)) IF ( len(isnull(@IPAddress,'')) = 0 OR isnumeric(replace(@IPAddress, '.', '')) = 0 OR EXISTS (SELECT 1 WHERE @IPAddress NOT LIKE '%.%.%.%') OR EXISTS (SELECT 1 WHERE @IPAddress LIKE '%..%') OR EXISTS (SELECT 1 WHERE @IPAddress LIKE '%.%.%.%.%') OR EXISTS (SELECT 1 WHERE @IPAddress LIKE '.%' OR @IPAddress LIKE '%.') ) BEGIN SELECT 0 END ELSE BEGIN SELECT 1 END
T-SQL:How to get a default value from a select statement that may return a null

In some instances I've had a variable I need to fill with a value, and wanted to insure that it is NEVER null. As an example, suppose you want to fill a variable with the PKID of a record based upon some search criteria, but, if no matching record is found, I want the variable to have "some value", instead of null. If you try to use ISNULL as part of the select/assignment, and no record is found, the variable will STILL be NULL!

-- -- @myvar WILL be null if no matching record is found -- DECLARE @myvar INT SELECT @myvar = ISNULL(pkid,-1) FROM taba WHERE cola LIKE 'FindThis'

If you need to insure that you always get "some" value from a select statement, there are two ways to do this.

First, you can create a variable, run a SELECT statement to fill it, and then run a second select with the ISNULL function to insure it has some value.

DECLARE @myvar INT SELECT @myvar = pkid FROM taba WHERE cola LIKE 'FindThis' SELECT @myvar = ISNULL(@myvar,-1)

Second, you can wrap the SELECT statement WITHIN the ISNULL function.
Note: it is CRITICAL that the SELECT be enclosed within parenthesis.

DECLARE @myvar INT SELECT @myvar = ISNULL( (SELECT pkid FROM taba WHERE cola LIKE 'FindThis') ,-1)
T-SQL:How to format an int/integer/number with comma separators

While it amazes all of us that Microsoft has yet to introduce a "format" command in T-SQL, the fact is you have to be creative in order to format 1000000 into 1,000,000. The trick is to convert it to a money, then a varchar, then trim off the ".00" that comes with a money type.

SELECT Left(convert(varchar(20),convert(money,xint) ,1) ,len(convert(varchar(20),convert(money,xint),1)) -3) AS IntWithCommas FROM (SELECT 123456789 AS xint) AS z IntWithCommas ------------------- 123,456,789
T-SQL:How to query or search an image or varbinary column/datatype for a string

Ok this one was annoying! Someone had a table with an "image" column and wanted search it for a value. The value was being expressed as '000005' (don't ask me what it represented...) Now to search an image (varbinary) you have to convert it to a varchar(max) then search that for the hexadecimal string equivilent of the search value. Sounds nuts, but, such is the life of a DBA.

The answer: use the Microsoft Supplied undocumented user defined function (udf) fn_varbintohexstr

SELECT * FROM usertable WHERE master.dbo.fn_varbintohexstr(data) LIKE '%000005%'
Keep in mind that fn_varbintohexstr is an “undocumented” function. Which means it may or may not be included in later versions of SQL
Home | TechNet | ADO.Net | DOS | ASP.NET | IIS | VB.NET | VIM (vi) | Windows | XHtml
MS-SQL | T-SQL | SSIS | Oracle