Miscellaneous Suggested Query Solutions

Here are several scenarios with a suggested Transact-SQL solution for accomplishing the goal:

Return the Maximum Value Between Two Parameters

For example, the maximum between 1 and 2 returns 2 and the maximum between 0 and -2 returns 0.

• Use CASE. For example, to return either the value of the royalty column or the hirange column, whichever is higher:

  USE pubs

  GO

  SELECT 'Higher Column Value' =

  CASE

  WHEN hirange >= royalty THEN hirange

  ELSE royalty

  END

  FROM roysched

  GO

    

Return the First 8,000 Characters of a text Column when Using a Text Pointer

To obtain the first 8,000 bytes of a text column, use any of these functions:

• CAST
• CONVERT
• SUBSTRING

Pad Numbers with Leading Zeros when Converting to Unicode and Character Strings.

Use the REPLICATE and DATALENGTH functions to left pad numbers to a specified length when converting from a numeric data type to character or Unicode. You need to allow for variable numbers of digits, so need variable numbers of padding zeros.

For example, the values 2, 37, and 597 must be padded to 002, 037, and 597:

USE Northwind

GO

DROP TABLE t1

GO

CREATE TABLE t1

(

 c1 varchar(3),

 c2 char(3)

)

GO

INSERT INTO t1 VALUES ('2', '2')

INSERT INTO t1 VALUES ('37', '37')

INSERT INTO t1 VALUES ('597', '597')

GO

SELECT REPLICATE('0', 3 - DATALENGTH(c1)) + c1 AS [Varchar Column],

       REPLICATE('0', 3 - DATALENGTH(c2)) + c2 AS [Char Column]

FROM t1

GO

See Also