SQL In-built Functions Part-1


SQL Functions | Academy4Code.com

Continue – To Part – 2(SQL In-built Functions Part-2)

Continue – To Part – 3(SQL In-built Functions Part-3)

  • 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()

  1. Syntax what to find,
  2. Syntax to Search,
  3. 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)

Examples:

SQL Functions | CharIndex
SQL Functions | CharIndex

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

SQL Functions | CharIndex
SQL Functions | CharIndex

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.

SQL Functions | CharIndex
SQL Functions | CharIndex

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)

Example:

SQL Functions | Concat Function
SQL Functions | Concat Function

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, ……)

SQL Functions | Concat Function
SQL Functions | Concat Function
  • What is SQL DataLength() Function?

DataLength function return the length of the Syntax or string as well as integer values.

Syntax: DATALENGTH( Syntax);

Example:

SQL Functions | DataLength Function
SQL Functions | DataLength Function

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.

Length | SQL Function - Academy4Code
Length | SQL Function – Academy4Code

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.

Length | SQL Function - Academy4Code
Length | SQL Function – Academy4Code

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.

Length | SQL Function - Academy4Code
Length | SQL Function – Academy4Code

 

Continue – To Part – 2(SQL In-built Functions Part-2)

Please loud at us, if you have any query over write at ravindra@academy4Code.com /academy4code@gmail.com

Ravindra Kumar is a Programmer and an independent consultant from India. He has been a part of the industry for more than 6 years. During his career, he has worked on mutiple projects of the USA and of India (Technology Evangelist at Microsoft). He received his Bachelors of Engineering from Punjan Technical University. He has been a regular speaker of SQL Sessions in Indian IT Company.

For more, you can consult at ravindra@Academy4Code.com