Commonly used SQL Queries | MSSQL Server

SQL Queries (Microsoft SQL server), today we see the most commonly used SQL queries in our daily developer’s life. So you must go through these queries and remember them.

1. How to Concatenate a string in SQL?

• SELECT ‘Hello’ || ‘World’ || ‘!’; –returns HelloWorld!
• SELECT CONCAT(‘Hello’, ‘World’); –returns ‘HelloWorld
• SELECT CONCAT(‘Hello’, ‘World’, ‘!’); –returns ‘HelloWorld!’
• SELECT CONCAT(‘Foo’, CAST(42 AS VARCHAR(5)), ‘Bar’); –returns ‘Foo42Bar’ 
• SELECT CONCAT(CONCAT(‘Foo’, 42), ‘Bar’) FROM dual; –returns Foo42Bar
• SELECT ‘Foo’ + CAST(42 AS VARCHAR(5)) + ‘Bar’;

2. How to count string Length in SQL Server?

i. The LEN doesn’t count the trailing space.

• SELECT LEN(‘Hello’) — returns 5
• SELECT LEN(‘Hello ‘); — returns 

ii. The DATALENGTH counts the trailing space.

• SELECT DATALENGTH(‘Hello’) — returns 5
• SELECT DATALENGTH(‘Hello ‘); — returns 6

3. How to Trim empty spaces in MSSQL? | SQL Queries

• SELECT LTRIM(RTRIM(‘ Hello ‘)) –returns ‘Hello’ 
• SELECT LTRIM(‘ Hello ‘) –returns ‘Hello ‘
• SELECT RTRIM(‘ Hello ‘) –returns ‘ Hello’ SELECT LTRIM(RTRIM(‘ Hello ‘)) –returns ‘Hello

4. How to convert a string in Upper & lower case in SQL?

• SELECT LOWER(‘HelloWorld’) –returns ‘helloworld’ 
• SELECT UPPER(‘HelloWorld’) –returns ‘HELLOWORLD’

5. How to SPLIT a string in SQL?

Splits a string expression using a character separator. Note that STRING_SPLIT() is a table-valued function. 

SELECT value FROM STRING_SPLIT(‘Lorem ipsum dolor sit amet.’, ‘ ‘); 

Result: 

value 
—– 
Lorem 
ipsum 
dolor 
sit 
Amet.

6. How to Replace a string in SQL?

Syntax: 

REPLACE( String to search, String to search for and replace, String to place into the original string )

Example: 

SELECT REPLACE( ‘Peter Steve Tom’, ‘Steve’, ‘Billy’ ) –Return Values: Peter Billy Tom

7. How to REVERSE a string in SQL?

SELECT REVERSE(‘Hello’) –returns olleH 

8. How to REPLICATE  a string in MSSQL?

The REPLICATE function concatenates a string with itself a specified number of times. 

The syntax is: 

REPLICATE ( string-expression, integer ) 

SELECT REPLICATE (‘Hello’,4) –returns ‘HelloHelloHelloHello’ 

9. How to use  INSTR in MSSQL?

Syntax:

INSTR ( string, substring ) 

SELECT INSTR(‘FooBarBar’, ‘Bar’) — return 4 

SELECT INSTR(‘FooBarBar’, ‘Xar’) — return 0

10. How to use PARSENAME in SQL Server?

The PARSENAME function returns the specific part of a given string(object name). Object names may contain strings like object names, owner names, database names, and server names.

Syntax:

 PARSENAME(‘NameOfStringToParse’,PartIndex)

Example:

SELECT PARSENAME(‘ServerName.DatabaseName.SchemaName.ObjectName’,1) // returns `ObjectName` 

SELECT PARSENAME(‘[1012-1111].SchoolDatabase.school.Student’,1) // returns `Student`