Hello, Nirav!

Here you go:

Statement 01: select Substring('Nirav',1,4)

The output is 'Nira' - SQL Server will start from the 1st character, and return a total of 4 characters. (Please note that unlike C++ or other programming languages, string manipulation in SQL Server does not use a 0-based index)

Statement 02: SELECT SUBSTRING('Nirav',-2,4)

This is same as the Statement 01. If the start position is negative, SQL Server gracefully handles this scenario and starts from the beginning of the string.

Statement 03: SELECT SUBSTRING('Nirav',4,-2)

The length has to be a positive integer. Since the length parameter here is (-2), SQL Server will generate an error (which one, I don't know because for that I would have to use SSMS :))

By the way - this would make a good T-SQL Interview question. You may want to repost this one (or provide a link to this post) in the Interview Questions module of the site (http://beyondrelational.com/modules/17/interview-questions.aspx?s=stream&tab=interview-questions).

HI,Nakul

Statement 02 and Statement 01 not give same output.

@Nirav: Agree. Another look at Books-On-Line cleared the formula.

Statement 02: SELECT SUBSTRING('Nirav',-2,4)

If the start position is negative, SQL Server gracefully handles this scenario and starts from the beginning of the string. However, the number of characters returned are (start position + length - 1), which in this case evaluates to (-2 + 4 -1) = 1.

The output will therefore be 'N'

yes..u r right..

Thanks for give answer....