The following queries demonstrate the T-SQL SUBSTRING function. I’ve also included a comparison with the C# function of the same name.
Unlike C#, the T-SQL SUBSTRING is 1 based.
print substring('a,b,c,d', 1, 1);
-- RETURNS: a
Unlike C#, SUBSTRING’s 3rd parameter is NOT optional. You can’t leave it blank if you want to match all the remaining characters like you could in C#. However, you can use DATALENGTH(string) to return all remaining characters in the string, even though there are obviously fewer than DATALENGTH(string) characters remaining in the string.
print substring('a,b,c,d', 3, DATALENGTH('a,b,c,d'));
-- RETURNS: b,c,d
A value of 10 for the third parameter is acceptable, even though there aren’t 10 characters in the string.
print substring('a,b,c,d', 3, 10);
-- RETURNS: b,c,d
START POSITION can be negative.
print substring('a,b,c,d', -3, DATALENGTH('a,b,c,d'))
-- RETURNS: a,b
Questions or comments about this script? Be a part of the conversation. It only takes a minute to post a comment.
In this Script
Returns the number of bytes used to represent any expression.
DATALENGTH ( expression )
/* Examples */ -- Uses AdventureWorks SELECT length = DATALENGTH(EnglishProductName), EnglishProductName FROM dbo.DimProduct ORDER BY EnglishProductName; GO
Related Topics: CAST and CONVERT Data Types LEN System Functions
Data Type Functions: IDENT_CURRENT IDENT_INCR IDENT_SEED SQL_VARIANT_PROPERTY
Returns part of a character, binary, text, or image expression in SQL Server.
SUBSTRING ( expression ,start , length )
/* A. Using SUBSTRING with a character string */ SELECT name, SUBSTRING(name, 1, 1) AS Initial , SUBSTRING(name, 3, 2) AS ThirdAndFourthCharacters FROM sys.databases WHERE database_id < 5; /* B. Using SUBSTRING with text, ntext, and image data */ USE pubs; SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo, SUBSTRING(pr_info, 1, 10) AS pr_info FROM pub_info WHERE pub_id = '1756'; /* C. Using SUBSTRING with a character string */ -- Uses AdventureWorks SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial FROM dbo.DimEmployee WHERE LastName LIKE 'Bar%' ORDER BY LastName;
Related Topics: LEFT LTRIM RIGHT RTRIM String Functions STRING_SPLIT TRIM
String Functions: ASCII CHAR CHARINDEX CONCAT CONCAT_WS DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX
Returns a user-defined message to the client.
PRINT msg_str | @local_variable | string_expr
/*
A. Conditionally executing print (IF EXISTS)
*/
IF @@OPTIONS & 512 <> 0
PRINT N'This user has SET NOCOUNT turned ON.';
ELSE
PRINT N'This user has SET NOCOUNT turned OFF.';
GO
/*
B. Building and displaying a string
*/
-- Build the message text by concatenating
-- strings and expressions.
PRINT N'This message was printed on '
+ RTRIM(CAST(GETDATE() AS nvarchar(30)))
+ N'.';
GO
-- This example shows building the message text
-- in a variable and then passing it to PRINT.
-- This was required in SQL Server 7.0 or earlier.
DECLARE @PrintMessage nvarchar(50);
SET @PrintMessage = N'This message was printed on '
+ RTRIM(CAST(GETDATE() AS nvarchar(30)))
+ N'.';
PRINT @PrintMessage;
GO
/*
C. Conditionally executing print
*/
IF DB_ID() = 1
PRINT N'The current database is master.';
ELSE
PRINT N'The current database is not master.';
GO
Related Topics: Data Types DECLARE @local_variable RAISERROR
T-SQL Language Elements: BEGIN DISTRIBUTED TRANSACTION BEGIN TRANSACTION BEGIN…END BREAK CASE CLOSE COALESCE COMMIT TRANSACTION COMMIT WORK CONTINUE CREATE DIAGNOSTICS SESSION DEALLOCATE DECLARE CURSOR EXCEPT and INTERSECT EXECUTE FETCH GO GOTO IF…ELSE NULL and UNKNOWN
About the Archive This script is part of my personal code snippet library, which I’ve been posting to this site using the WordPress automation processes I’m working on.
