Issues using Substring to extract characters

Question

I am using the following:
CASE WHEN CHARINDEX('<' , UserDefined96) > 0 THEN SUBSTRING(UserDefined96 , CHARINDEX('<' , UserDefined96) + 1 , CHARINDEX('>' , UserDefined96) - CHARINDEX('<' , UserDefined96) - 1) ELSE UserDefined96 END

Basically, it looks to see if the < character exists, and if so, it attempts to extract what's between < and > . For example, the cell contains Joe Smith <jsmith@mathieu.company .org>. I want the results to just be jsmith@mathieu.company .org. I need to ultimately use this as part of a REPLACE statement to fix around 2500 cells that are invalid at this point, but I cant seem to get past this part. Without the final CHARINDEX, I am left with the ending >. But with it, I am getting an error "Invalid Length Parameter passed to the LEFT or SUBSTRING function" If < exists in this table, then > always exists, just FYI.R

Worst case, I guess I could possibly do a replace statement leaving the ending >, and than run another full update just looking for and removing >, but it seems like this should work without going through that.

Any suggestions?

Answer 1

Try this

DECLARE @tbl TABLE (Val VARCHAR(500));  
  
INSERT INTO @tbl (Val)  
VALUES  
	('<******@abc.com>')  
	,('******@nothing.com')  
	,('<******@abc.com')  
	,('><******@abc.com')  
  
  
  
  
SELECT  
	CASE WHEN CHARINDEX('>',Val)>CHARINDEX('<',Val) THEN  
	SUBSTRING(Val, CHARINDEX('<', Val) + 1, CHARINDEX('>', Val, CHARINDEX('>', Val)- 1)  - CHARINDEX('<', Val) - 1)  
	ELSE Val END as betweenval  
FROM @tbl  
  

Answer 2

HI @Jeff Irish ,

Please try the following solution. It will work starting from SQL Server 2017 onwards.

SQL

 DECLARE @tbl TABLE (Val VARCHAR(500));  
 INSERT INTO @tbl (Val) VALUES  
('<******@abc.com>'),  
('******@nothing.com'),  
('<******@abc.com'),  
('><******@abc.com');  
  
SELECT *   
	, TRIM('<>' FROM Val) AS Result  
FROM @tbl;  

Output

+--------------------+------------------+  
|        Val         |      Result      |  
+--------------------+------------------+  
| <******@abc.com> | ******@abc.com |  
| ******@nothing.com   | ******@nothing.com |  
| <******@abc.com  | ******@abc.com |  
| ><******@abc.com | ******@abc.com |  
+--------------------+------------------+