Trim leading blank space within CASE statement

[madanthrax]

Hi all,

I have a select statement with case in it which you guys helped me with a few months ago. Its been running fine, but recently the inputs to the field that is the subject of the case function (SeriesInfo) have been getting a blank leading space which messes up the character count (see code below).

Unfortunately the inputs to the MSSQL database are done by another department which has recently started employing monkeys to input the data, who only seem to use copy/paste. Repeated attempts to get them to cooperate have failed. I would like to use the trim function but have no idea what I am doing, or even if its possible within a case statement.

Here is the code with an attempted trim added to it (not working):

SELECT   TOP (100) PERCENT PubId, Title, Language, LanguageName, ElectronicFile, FileSize, SeriesInfo, Released, Issued, StatusId, Type, SeriesText, CASE WHEN SUBSTRING(trim(SeriesInfo, 4, 1)) = 'R' THEN '1' WHEN SUBSTRING(SeriesInfo, 3, 1) = 'R' THEN '1' WHEN SUBSTRING(SeriesInfo, 1, 4) = 'SF-1' THEN '3' ELSE '2' END AS Category

The select is running in a view, and the case part is to add an extra column to the view that detects what category of document each row is (1,2,or 3).

So is it possible to trim the SeriesInfo field please, and if so what would be the syntax?

Thanks in advance for any help you can give me.

Anthony.

_{"Nothing is impossible until proven otherwise"}​

 

[dhulbert]

If you change the trim be be Ltrim(Rtrim(SeriesInfo)) that will strip an dleading or trailing spaces.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[madanthrax]

Thanks dhulbert.

In case anyone is interested in the solution, this worked for me.
Using LTRIM in the case statement was not possible so I used it on the SeriesInfo field in the select statement. This code was accepted by SQL Server Man. Studio but somehow the SUBSTRING was still reading the untrimmed value. I then made view to go between the source data and the view under discussion in which I placed "LTRIM(SeriesInfo) AS SeriesInfo," in the select statement. This worked fine.

_{"Nothing is impossible until proven otherwise"}​