Tip of the Day : Example Uses of the FLOOR Mathematical Function

# SQL Server Helper - Tip of the Day

### Example Uses of the REPLACE String Function

The REPLACE string function replaces all occurrences of the second specified string expression in the first string expression with a third expression. The syntax of the REPLACE string function is as follows:

`REPLACE ( <string_expression_1>, <string_expression_2>, <string_expression_3> )`

The first parameter of the REPLACE string function, <string_expression_1>, is the string expression to be searched, the second parameter, <string_expression_2>, is the string expression to try to find, and the third parameter, <string_expression_3>, is the replacement string expression.  All three parameters can be of character or binary data.  If any of the parameters is NULL, the REPLACE string function returns NULL.

Here are a few examples on the uses of the REPLACE string function:

Usage #1 : Count the Occurrence of a Character

```DECLARE @Input VARCHAR(50) = 'SHE SELLS SEASHELLS AT THE SEASHORE'
DECLARE @Char CHAR(1) = 'E'
SELECT LEN(@Input) - LEN(REPLACE(@Input, @Char, '')) AS [CharCount]
```
```CharCount
-----------
7
```

Usage #2 : Count the Occurrence of a String

```DECLARE @Input VARCHAR(50) = 'I SCREAM, YOU SCREAM, WE ALL SCREAM FOR ICE CREAM'
DECLARE @String VARCHAR(10) = 'SCREAM'
SELECT (LEN(@Input) - LEN(REPLACE(@Input, @String, ''))) / LEN(@String) AS [StringCount]
```
```StringCount
-------------
3
```

Usage #3 : Count the Number of Words Within a String

```DECLARE @Input VARCHAR(50) = 'SHE SELLS SEASHELLS AT THE SEASHORE'
SELECT LEN(@Input) - LEN(REPLACE(@Input, ' ', '')) + 1 AS [WordCount]
```
```WordCount
-----------
6
```

Usage #4 : Trim Leading Zeros

```DECLARE @Input VARCHAR(20)
SET @Input = '0000120-3100'
SELECT REPLACE(LTRIM(REPLACE(@Input, '0', ' ')), ' ', '0')
```

Usage #5 : String Character Translation

```DECLARE @Input VARCHAR(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @Output VARCHAR(26) = '!@#\$%^&*()[]{}\|;:<>,./?`~'
DECLARE @String VARCHAR(50) = 'THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG'

SELECT @String = REPLACE(@String, SUBSTRING(@Input, [Number], 1), SUBSTRING(@Output, [Number], 1))
FROM [master].[dbo].[spt_values]
WHERE [Type] = 'P' AND [Number] != 0 AND [Number] <= LEN(@Input)
SELECT @String AS [String]
```
```String
----------------------------------------------
>*% ;,(#[ @:\/} ^\? ),{|< \.%: >*% ]!~` \$\&
```

 Back to Tip of the Day List Next Tip