SQL ServerTSQLFunctions

SQL Functions

SQL Server includes a programming language. That language supports the use of functions. You can create functions, or you can use functions built into SQL.

This article summarizes some of the built in functions.

String functions

COALESCE ( @Variable, ColumnName )

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
UPDATE dbo.DCN
SET MaximoWO = COALESCE(@Maximo, MaximoWO),
                descRequest = COALESCE(@Desc, descRequest),
                cdDCNType = COALESCE(@cType, cdDCNType),
                Area = COALESCE(@Area, Area),
                ESPNUpdate = COALESCE(@ESPN, ESPNUpdate),
                cdAction = CASE
                                                                    WHEN @Assignee IS NOT NULL THEN
                                                                                    9
                                                                    ELSE
                                                                                    cdAction
                                                    END,
                FacilityID = COALESCE(@FacilityID, FacilityID),
                MOC = COALESCE(@MOC, MOC),
                ESPNRequester = COALESCE(@Requester, ESPNRequester)
FROM dbo.DCN
WHERE dcnId = @dcnID;

In this case MOC = if @MOC is null then put in MOC, if @MOC has a value, then use that instead.

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

returns starting position

PARSENAME (‘object_name’ , object_piece )

Wsed to split dayoff.sasdddb1.dbo.wo - into pieces 1, 2, 3, 4. But people use it as a general parsing tool.

where
– 1 = Object name
– 2 = Schema name
– 3 = Database name
– 4 = Server name

STRING_SPLIT ( string , separator )

Stuff (Character_Expression, Start, Length, ReplaceWithExpression)

Use this function to insert a string into another string. It will cut out Length characters starting at position Start (which is one based). In that cut point it will insert the entire ReplaceWithExpression

1
2
3
4
ex (from reference)
    SELECT STUFF('abcdef', 2, 3, 'ijklmn');  
    ---------   
    aijklmnef

SUBSTRING ( expression ,start , length )

Used to pull out part of a string from another.

reference