ProjectSMM.com
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 = '127.0.0.1'
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