LEFT(string, length)
RIGHT(string, length)
The string can be anything that contains string data: a variable containing a VARCHAR or NVARCHAR, a literal VARCHAR or NVARCHAR, or a column with a type of VARCHAR or NVARCHAR, etc. Are you sensing a pattern here? You can't take the left 3 characters of an INT, or a DATETIME, or a TEXT field. It may seem obvious why you can't do this operation on a number, but you may ask why you can't use these functions on a TEXT field.
UPPER(string)
LOWER(string)
LTRIM and RTRIM
LTRIM(string)
RTRIM(string)
You pass it a string (CHAR, NCHAR, VARCHAR, NVARCHAR), and it takes off either the leading spaces, or the trailing spaces. You shouldn't need to use RTIM on a variable length character string.
REPLICATE(string, length)
The string is a VARCHAR, or something implicitly CONVERTable to a VARCHAR. This means SQL has to know how to turn the string you pass into a VARCHAR, otherwise you'll get an error. The length is a positive INTEGER telling SQL how many of the character you want returned.
REPLACE(searchedString, seekString, replaceWithString)
Basically you first pass the string you're looking through to find the seekString. If SQL finds the seekString, it will replace that with the replaceWithString. This is great for more advanced lookups. Especially when you couple it with the wildcard searches I showed you in
a past post. You can do some pretty impressive things. The following isn't an example of an awesome - cool replace, but a simple one.
SELECT REPLACE('Luke''s Lightsaber was green in Episode 4.','green','blue')
--this should return:
Luke's Lightsaber was blue in Episode 4.
--someone should call Lucas and remind him of this fact... Special Edition, meh!
Conclusion
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (15)
Commented:
LEFT( someVarcharField, 7 )
...means that only seven characters will need to travel from the server to the application (as opposed to, say, 1,000 -- of which the app will need to discard 993). Just a thought for the mix.
Commented:
Can you use LEFT$ for SQL as you can in Excel VBA? As a string function it outperforms the variant function LEFT
Cheers
Dave
Commented:
Commented:
Manipulation in SQL is the prefered way for Excel reporting.
Commented:
View More