Substring in SQL or Split the column value in SQL

Some times in SQL we need to use substring or split the selected value. For that the below methods we can use to split the string in the SQL Server. The Below table we have first name+ last name separated with | simple.  The below query I wrote to get First name from the names column. I used two methods to get the results.

SELECT * FROM [dbo].[MyTable]

ID            Names

1              vijay | sekar

2              antony | amal

3              yuvan | karthick

Splitting string using SUBSTRING options in SQL:

 Syntax:   SUBSTRING ( expression ,start , length )

Query:

SELECT SUBSTRING(Names,0,CHARINDEX('|',Names)) AS Result FROM MyTable

Result

vijay

antony

yuvan

Splitting string using LEFT, RIHGHT characters in SQL:

Syntax:  LEFT ( character_expression , integer_expression )

Query:

SELECT Names,(LEFT(Names,CHARINDEX('|',Names) -1)) AS LeftResult,

(RIGHT(Names,CHARINDEX('|',Names) -1)) AS RightResult FROM MyTable

Result

Names                                  LeftResult           RightResult

vijay | sekar                       vijay                        sekar

antony | amal                    antony                 | amal

yuvan | karthick               yuvan                    rthick

Based on your requirement you can use the splitting string option in SQL. I hope this will help you to solve your split string in SQL.

How to get string length in SQL?

The below query will help you to get the single columns string length. This is the one of easy way to get string count in SQL.

SELECT Names,LEN(Names) AS NameCount FROM MyTable

Result:

Names                                  NameCount

vijay | sekar                       13

antony | amal                    13

yuvan | karthick               16

Writing Single select query for multiple filter in sql where condition

In the stored produces some time we need to use multiple filters. Some have value and some don’t have any value. For that we usually write different IF condition to execute the select statement. In SQL have simple way to use one select statement to use multiple where condition or multiple filter.

CREATE Procedure [dbo].[YourSP]

@Empid varchar(50),

@Edept varchar(50)

As

Begin

IF @ Empid = ''

Set @ Empid = null

IF @ Edept= ''

Set @ Edept = null

select * from Employee Where ((@Empid is null or EmpidID = @ Empid) and (@Edept is null or EDept=@Edept))

End

Comma separated value in sql query

One of my projects I want to get city postal code as a one column with comma separated. I want two columns as an output. One is State and another one is commas separated postal code in the state.

The below query will help you to get the each state with comma separated postal code.

Code:

SELECT State,

      Post_Code =

        STUFF((SELECT ', ' + Post_Code

           FROM your_table b

           WHERE b. State = a. State

          FOR XML PATH('')), 1, 2, '')

FROM your_table a

GROUP BY State

OUTCOME:

State	Postal_Code
Tamilnadu	600001,600256,638656,638657
Kerala	678965,670059,670056,673598

More Details:

http://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20130628

WITH Query in SQL

In the sql most of the new developers don’t know how to use  WITH in SQL query.  Its simple and use full for writing big query in the stored procedure. WITH will provide a way of using sub query in the larger select query. It’s a kind of temp table in the sql query.  One use of this feature is to break down complex query into similar parts. The below is a simple example for with in querying.

;with table1 as

(

      select * from MYTable

),

Table2 as

 (

      select * from MyTable2

),

.

.

.

Table10 as

 (

      select * from MyTable99

)

select * from table1

inner join Table2.ID=table1.ID

Left Outer join table1.ID=Table13.ID

In the final select query we can use sum , group by and etc…

The WITH Common Table Expression (CTE) is used for creating temporary resultsets. This functionality indroudeced in SQL SERVER 2005.