CODEWITHSUNDEEP
t-sqltrimspaces
JMS49
JMS49

Reputation: 273

T-SQL syntax issue with "LTRIM(RTRIM())" not working correctly

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. What am I 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

Upvotes: 1

Views: 4421

Answers (4)

akonsu
akonsu

Reputation: 29566

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 Microsoft SQL Server (you are using Microsoft SQL Server it seems) using the built-in functions. There are some examples on the web that use a 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 the CLR and invoke it from the select statement.

I hope this helps.

Upvotes: 0

KMW
KMW

Reputation: 109

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

Upvotes: 1

websch01ar
websch01ar

Reputation: 2123

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

Upvotes: 0

bobs
bobs

Reputation: 22204

Replace TRIM with LTRIM.

You can also test LEN(cShortName) = 0

Upvotes: 0

Related Questions