What is LEN in Transact-SQL? It will returns the number of characters of the specified string expression, excluding trailing blanks.
DECLARE @temp varchar(60);
SET @temp = ‘ABCDEF ‘;
The returned result is ’6′. It returns the count of 6 characters “ABCDEF”.
What is the function of RTRIM? It will returns a character string after truncating all trailing blanks.
DECLARE @string_to_trim varchar(60);
SET @string_to_trim = ‘Four spaces are after the period in this sentence. ‘;
SELECT @string_to_trim + ‘ Next string.’;
SELECT RTRIM(@string_to_trim) + ‘ Next string.’;
The result == “Four spaces are after the period in this sentence. Next string.“. As you can see, the 4 spaces behind the word ‘sentence’ was trim off.
SUBSTRING will return part of the original string based on the expressions.
SUBSTRING (value_expression, start_expression, length_expression)
SELECT x = SUBSTRING(‘ABCDEFGHIJ’, 3, 5);
The returned result is “CDEFG”. It will start a index ’3′ and with length ’5′ characters.
What is CHARINDEX use for? It will return the starting position if the searched value is found. For example,
CHARINDEX (SearchedValue, StringToBeSeached)
StringToBeSeached == “I like to eat durian”
SearchedValue == “eat”
The result will be ’11′. The word “eat” start on index 11.
How to delete / drop SQL table? The following code will guide you to drop the temporary SQL table. Basically, you just need to type in ‘DROP TABLE tableName‘.
IF OBJECT_ID(N’tempdb..#temptable’, N’U') IS NOT NULL
DROP TABLE #temptable;