In the below query, the SUBSTRING() is used along with the column ‘NAME’ from the ‘STUDENTS’ table where the substring which will be extracted starts from the first character of the student’s name which has a length of 3.
Let us consider the below table ‘STUDENTS’ to understand how the SUBSTRING() function can be used with the columns of a table. by specifying the starting position or location and the length of the substrings. Similarly, we can extract any required substring by using the SUBSTRING() function accordingly i.e. The result of the above statement is shown below as the extracted substring is ‘Ho’. SELECT SUBSTRING('How are you?', 1, 2) AS Result The substring starts from the first character ‘H’ and has a length of 3, hence the extracted substring is How.Īlso, in the below query, it can be seen that substring is extracted from the first character ‘H’ and has a length of 2. SELECT SUBSTRING('How are you?', 1, 3) AS Result Īs a result, the substring which is extracted is ‘How’. The below query will extract the substring which will start from the first character of the given input string ‘How are you?’ and the length of the substring will be 3. In the below example, it can be seen that the SUBSTRING() function is used with the literal strings. Let us go through some examples to understand the working of SUBSTRING() in SQL Server and how they can be used within the queries. But when a length is a negative number, SUBSTRING() will throw an error.
When the value of start + length is greater than the length of the input string, the substring begins at the start which will also contain the remaining characters of the input string.
My recommendation would be to download Oracle and give it a try. Hopefully that didn’t confuse you too much. So if you did –20, the result would be nothing. In this case the length of this string is 20. The only catch is that you can’t go past the length of the string. That’s right, Oracle’s SUBSTR function can take a negative value. SELECT SUBSTR( ‘The quick brown fox.‘, -3, 3) FROM dual Here’s something that SQL Server can’t do with SUBSTRING. Looks the same as SQL Server, right? Well, Oracle is has something cool up it’s sleeve just as well. SELECT SUBSTR( ‘The quick brown fox.‘, 5, 5) FROM dual If you’re coming from a SQL Server and have worked with SUBSTRING quire a bit then you may feel you’ve already mastered the SUBSTR() function in Oracle but, you haven’t. These two functions take either the left N number of characters or the right N number of characters, giving more options for would be SQL coders. Substring isn’t it though SQL Server has a few tricks up it’s sleeve, LEFT() & RIGHT(). SELECT SUBSTRING( ‘The quick brown fox.‘, 5, 5) Pass in a string or column, tell it where to start, give it a number of characters you want to return. SQL Server’s substring function is really straight forward and works just like you’d think.
If you’ve been working with databases for a while, I’m sure you’ve had to parse a string and while you’d think these are the same they work a little different and I think Oracle may surprise you a bit. Continuing the comparison between these two database giants, we dive into the substring function.