Substring() in SqlServer

SubString function:

This function is used to get the specified length of string based on specification of start position and required length of string. Generally SubString function will take 3 arguments.

Declaration of SubString function:

SUBSTRING(string, startIndex, length)

In this function

1st Argument specifies the string value

2nd Argument specifies the start position of string from where part of string begins

3rd Argument specifies number of characters to be returned from the specified string

ex:

SELECT OUT = SUBSTRING('abcdefgh', 2, 3)
The output will be "bcd".

SELECT SUBSTRING('STRING FUNCTION', 1, 6)
Result : STRING
SELECT SUBSTRING('STRING FUNCTION', 8, 8)
 Result : FUNCTION

--------------------------------------------------------------------------------------------------



substring();

substring function return some part of string from the string

Syntax:

SUBSTRING(expression,start,length)


DECLARE @ImportDate VARCHAR(8) = '10122012'

SELECT CAST(SUBSTRING(@ImportDate, 5, 4) + SUBSTRING(@ImportDate, 3, 2) + SUBSTRING(@ImportDate, 1, 2) AS DATETIME)
       
   Result:
       
2012-12-10 00:00:00.000


you also write queries like this.....


SET DATEFORMAT DMY
DECLARE @ImportDate VARCHAR(8) = '10122012'

SELECT CAST(STUFF(STUFF(@ImportDate, 5, 0, '-'), 3, 0, '-') AS DATETIME)

SET DATEFORMAT MDY

Result:

2012-12-10 00:00:00.000


--------------------------------------------------------

SET DATEFORMAT DMY

SELECT CAST(STUFF(STUFF('10122012', 5, 0, '-'), 3, 0, '-') AS DATETIME)

SET DATEFORMAT MDY


Result:2012-12-10 00:00:00.000
----------------------------------------------------------



Substring syntax


Substring (expression,startpoint,lenght)

99 represents thee Length of the expression
99 reflects the potential length of a string between the 1st comma and the second comma


declare @string varchar(20)
set @string='A,B,C'

Select substring( SUBSTRING(@string,charindex(',',@string)+1,99),0,charindex(',',SUBSTRING(@string,charindex(',',@string)+1,99)))

Result:
B

------------------------------------------------------------------------------------------------------------------------

create table col (col varchar(255))

insert into col
 select 'A,B,C'
 union all
select 'AA,BBBB,CCC'
union all
select 'AAA,BBBBBBBBBBBBBBBBBBBBBBBBBBBBB,CCC'
union all
select 'AAAAAAA,BBBBBBBB,CCC'

Select substring( SUBSTRING(col,charindex(',',col)+1,99),0,charindex(',',SUBSTRING(col,charindex(',',col)+1,99))) from col

drop table col

Result:
B
BBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBB