SQL In-built Functions Part-3


SQL Functions | Academy4Code.com
  • What is SQL NCHAR Function?

NCHAR function returns the Unicode character based on the number_code in the Unicode standard.

Syntax: NCHAR(Number code)

Example:

NCHAR | SQL Function - Academy4Code
NCHAR | SQL Function – Academy4Code

The above query returns the Z character which code is 90 in ASCII.

  • What is SQL PATINDEX Function?

The PATINDEX functions returns the location of a pattern in a string. The search is not case-sensitive.

Syntax: PATINDEX(‘%S%’, string)

Example:

PATINDEX | SQL Function - Academy4Code
PATINDEX | SQL Function – Academy4Code

The above query returns 2 the position of y character from a string.

PATINDEX | SQL Function - Academy4Code
PATINDEX | SQL Function – Academy4Code

If we try to find the char position which is not available in the string then it will be return the 0 position always. Mentioned in above query.

 

  • What is SQL REPLACE Function?

The REPLACE function replaces a sequence of characters in a string with another set of characters, not case-sensitive. Replace function contain three parameters.

Syntax: REPLACE(String1,String text to replace, Replacement string text)

Example:

Replace | SQL Function - Academy4Code
Replace | SQL Function – Academy4Code

The above query returns the string value where ‘o’ is Replace with ‘5’ and finally string value become “WWW.G55gle.C5m”.

  • What is SQL STR() Function?

The STR Function return a string representation of a number.

STR function contain three parameters (i) Number values (ii) Length you want to display (iii) Decimal Places. Two parameters are optional.

Syntax: STR(Number,Length,Decimal_Places)

Example:

STR | SQL Function - Academy4Code
STR | SQL Function – Academy4Code

The above query show 123.5 STR() function round of value returns 124.

STR | SQL Function - Academy4Code
STR | SQL Function – Academy4Code

The above query returns the 123.46 value. We defined in the STR Function that 7 values display and 2 means only 2 decimals. So actual value in string is (123.456,7,2) and finally after STR function 123.46.

STR | SQL Function - Academy4Code
STR | SQL Function – Academy4Code

If we define two parameters in STR function, then it will always return the round of value because decimal default value is 0.

  • What is SQL STUFF() Function?

 

Stuff function allow us to replace the string value with new string or some string text with new one.

Stuff function contain four parameters STUFF(String,start position to replace string,Total charater replace, string text which you want to replace).

Syntax: STUFF(String,Int start position, Int total character replce,String replacement)

Example:

STUFF | SQL Function - Academy4Code
STUFF | SQL Function – Academy4Code

The above query show that STUFF Function allow to replace position started from 1 char and 4 character will be replace with “WWWW” string value.

  • What is SQL SUBSTRING() Function?

The SUBSTRING functions allows you to extract a substring from a string.

SUBSTRING Contain three parameter

(i) String

(ii) Integer Position Started

(iii)Integer for how much character to show

Syntax: SUBSTRING(String, Integer Start Position, Integer to display Characters)

Example:

SUBSTRING | SQL Function - Academy4Code
SUBSTRING | SQL Function – Academy4Code

The above query returns first 4 character of the string. We have mentioned in parameter 2nd parameter 1 which means start position 1 and third parameter is 4 which means display 4 characters from the start of String.

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

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

Thanks for reading the article.

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