SQL Server function tips && integer type null is an empty string substitution implementation

1. Empty function

Description: Replaces NULL with the specified replacement value.

Syntax:ISNULL (check_expression, Replacement_value)

Parameters:

Check_expression: An expression that will be checked for NULL. Check_expression can be of any type.

Replacement_value: An expression to return when check_expression is null. The Replacement_value must be a type that can be implicitly converted to the check_expresssion type.

Return value: Returns the same type as check_expression.

Note: If Check_expression is not NULL, its value is returned, otherwise the Replacement_value is implicitly converted to a type of check_expression (if the two types are different), the former is returned.

Instance:

Add the Flag1 of type int to one, then replace it with 0

ISNULL (Flag1,0)   // add FLAG1 of type int to one, then use 0 instead of its value

　　Note : Since Flag1 is an integer (int) type, the latter value must also be of type int, otherwise it cannot be passed. Because if a data is empty, upload to the page will appear undefined, for the programmer, this is very normal thing, very clear know that it is a null value, but for ordinary users, undefined is very confusing, how can we in a shape field is empty, Replace with an empty string? With IsNull must not be solved, do not worry, we can use IsNull, case when and conver function to solve.

2. Case

Two formats for case

1. Simple Case function

 case   sex  when   " 1    then     male            when    2    then     female    else     other    end  

2.Case search function

 Case  when = ' 1 '  Then ' male '          when = ' 2 '  Then ' female ' ELSE ' other ' END

1). The case search function is more powerful than the other.

2). The case function is similar to the If......else if statement, returning only the first qualifying value, followed by the part being ignored

3. Convert function

The CONVERT () function is a general function that converts a date to a new data type.

The CONVERT () function can display date/time data in different formats.

Grammar:

CONVERT (data_type (length), Data_to_be_converted,style)

　

data_type (length) Specifies the target data type (with optional lengths). The data_to_be_converted contains values that need to be converted. style Specifies the date/time output format.

The style value that can be used:

Style ID	Style Format
100 or 0	Mon dd yyyy hh:miam (or PM)
101	Mm/dd/yy
102	Yy.mm.dd
103	Dd/mm/yy
104	Dd.mm.yy
105	Dd-mm-yy
106	DD Mon yy
107	Mon DD, yy
108	Hh:mm:ss
109 or 9	Mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	Mm-dd-yy
111	Yy/mm/dd
112	Yymmdd
113 or 13	DD Mon yyyy hh:mm:ss:mmm (24h)
114	Hh:mi:ss:mmm (24h)
120 or 20	Yyyy-mm-dd Hh:mi:ss (24h)
121 or 21	Yyyy-mm-dd hh:mi:ss.mmm (24h)
126	Yyyy-mm-ddthh:mm:ss.mmm (no spaces)
130	DD Mon yyyy hh:mi:ss:mmmAM
131	Dd/mm/yy Hh:mi:ss:mmmAM

Instance

The following script uses the CONVERT () function to display different formats. We will use the GETDATE () function to get the current date/time:

CONVERT(VARCHAR( +),GETDATE())CONVERT(VARCHAR(Ten),GETDATE(), the) CONVERT(VARCHAR( One),GETDATE(),106)CONVERT(VARCHAR( -),GETDATE(),113)

The results are similar:

Dec  in  -  One: $PM A- in- - in Dec  , in Dec  -  -: -:46.635

The CONVERT () function converts one type of data to another data type.

Convert(the data type to convert to, field name)

Example: converting an int type FLAG1 to a varchar (10) type

Convert (varchar(), FLAG1)

4, solve the different types of int type is empty, use empty string instead

1, if it is empty, it will be converted to 0

2, because the empty string is also a string, so when FLAG1 is not empty, still need to convert it to a string, so regardless of whether the Flag1 is empty, the result is a string, so this can be run

 Case   when 0 = ISNULL (Flag1,0)    Then ' ' Else Convert (varchar(), FLAG1) End

Think out of their own, may be less efficient, if there is a better way, hope enlighten!

SQL Server function tips && integer type null is an empty string substitution implementation