T-sql Substring - Last 3 Characters

Anticipating variable string lengths and NULL values

Dealing with unexpected lengths or those sneaky NULL values? Consider these preventive measures to keep your queries from derailing:

• Confirm length to eschew errors:

  SELECT CASE WHEN LEN(column_name) >= 3 THEN RIGHT(column_name, 3) ELSE column_name END FROM table_name; -- Length matters in this context! 
  

• Make NULL bite the dust with the COALESCE function:

  SELECT RIGHT(COALESCE(column_name, ''), 3) FROM table_name; -- Knock the NULL right out!
  

Exploring additional tactics

Got a bit of time on your hands to venture further into SQL territory? Let's dive into alternative strategies for when you're up against variable scenarios:

Pairing SUBSTRING with LEN

Promote control and drive performance with this dynamic duo:

SELECT SUBSTRING(column_name, LEN(column_name) - 2, 3) FROM table_name; -- Now that's a SUBSTRING with benefits!


The double whammy: REVERSE then SUBSTRING

For when your call of duty involves complexity, REVERSE() might just be the trick you need:

SELECT SUBSTRING(REVERSE(column_name), 1, 3) FROM table_name; -- Doing backflips with SQL!


And to restore original order, recharge with another REVERSE:

SELECT REVERSE(SUBSTRING(REVERSE(column_name), 1, 3)) FROM table_name; -- Flip it, flip it good!


Unleashing CHARINDEX

Locate any character from the edge and reel it in with CHARINDEX:

SELECT SUBSTRING(column_name, CHARINDEX('x', REVERSE(column_name)), 3) FROM table_name; -- CHARINDEX to the rescue!


Replace 'x' with your target character for specialized character extraction.

Maximizing your SQL prowess

For top-grade proficiency, remember these pointers when dealing with string operations:

• Wildcard is wild: Fixed-position extraction might go awry - best avoid wildcards.
• Different DBMS, different behaviours: These string methods might not play out the same way across various database systems, so testing is crucial.
• Performance tuning: RIGHT() is generally faster. But for complex string manipulations, SUBSTRING() just might steal the show.