- What is Sql String Functions?
SQL Function is a database object in Sql Server. Function accept only input parameters and return the result. Function can return only single value or a table. We can’t perform Insert, Update, Delete record on function in database.
- What is CharIndex() Function?
CharIndex() function is used to find the first or starting location of an Syntax or characters in a given string.
CharIndex() function contains three parameters CharIndex()
- Syntax what to find,
- Syntax to Search,
- Start location to find – the third parameter is an integer and it’s also an optional parameter.
Syntax: CHARINDEX ( Syntax_to_find, Syntax_to_search, start_location)
The query result is 16 which means that the char “e” start position is sixteen after start searching from the 6 character from a string because we have defined 6 (int) in third parameter in charindex function which means that start search of char in string from 6 character.
If we can’t define the third parameter in charindex function then it will be return the first match char position from a string
Let us assume we have a string “Wish you a very Happy New Years 2018”and we want to get the location of the word Years in the string. So the first parameter will be the word Years in the function.
The result of this query is 27, which means that the word Ago starts at location or position twenty-seven in the above string.
- What is SQL CONCAT Function?
Concat function allow us to concatenate the string together.
In simple word we can add the multiple string values into single string.
Syntax:– CONCAT (String, String,…. String n)
When you are concatenating/adding values together, you might want to add space characters to separate your concatenated values. Otherwise, you might get a long string with the concatenated values running together. This makes it very difficult to read the results.
Then we will use below Syntax: CONCAT(String, ‘ ’ , String, ……)
- What is SQL DataLength() Function?
DataLength function return the length of the Syntax or string as well as integer values.
Syntax: DATALENGTH( Syntax);
Now above query returns the result 21 and same Syntax uses in below query but its return 24 length of the Syntax or the String because below Syntax contains 3 space + string (3+21 = 24)
- What is SQL LEN() Function?
LEN function returns the length of the specified string. It is important to note that the LEN function does not include the space characters at the end the string when calculating the length but include the space character at the start of the string.
The above query result is 12 length of the string.
The above query also returns the length of string is 12 where three space included in the string but as we already clear to you that Len() function doesn’t include the end space of the string in calculation.
The above query result is 15 (Length of the string) but Observe the string is same but here three (3) spaces included at the start of the string.