TSQL syntax issue with LTRIM(RTRIM not working correctly

Question

What is wrong with this statement that it is still giving me spaces after the field. This makes me think that the syntax combining the when statements is off. My boss wants them combined in one statement can someone help me understand what I am doing wrong?

Case WHEN LTRIM(RTRIM(cSHortName))= '' Then NULL 
     WHEN cShortname is NOT NULL THEN 
       REPLACE (cShortName,SUBSTRING,(cShortName,PATINDEX('%A-Za-z0-9""},1,) ''_ 
end AS SHORT_NAME

Answer 1

why do you think it is supposed not to give you spaces after the field?

edit:

as far as i understand you are trying to remove any characters from the string that do not match this regular expression range [a-zA-Z0-9] (add any other characters that you want to preserve).

i see no clean way to do that in MSSQL (you are using MSSQL it seems) using the built in functions. there are some examples on the web that use temporary table and a while loop, but this is ugly. i would either return the strings as is and process them on the caller side, or write a function that does that using CLR and invoke it from the select statement.

hope this helps.

Answer 2

Replace TRIM with LTRIM.

You can also test LEN(cShortName) = 0

Answer 3

Ummm there seems to be some problems in this script, but try this.

Case 
     WHEN LTRIM(RTRIM(cSHortName))= '' Then NULL
     WHEN cShortname is NOT NULL THEN REPLACE(cShortName, SUBSTRING(cShortName, PATINDEX('%A-Za-z0-9', 1) , ''), '') 
end AS SHORT_NAME

Answer 4

Judging from the code, it seems that you may be trying to strip spaces and non-alphanumeric characters from the beginning and ending of the string.

If so, would this work for you? I think it provides the substring from the first alphanumeric occurrence to the last.

SELECT
SUBSTRING(
 cShortName,
 PATINDEX('%A-Za-z0-9',cShortName),
  ( LEN(cShortName)
   -PATINDEX('%A-Za-z0-9',REVERSE(cShortName))
   -PATINDEX('%A-Za-z0-9',cShortName)
  )
) AS SHORTNAME