First/Last Name order problems

[chazbadd]

I have a database (which I can't alter!) where people's first and last names are stored in a single column. I've written an ordey by statement (MSSQL 7) to order my output by the last name as below:


SELECT sName
FROM tblUsers
ORDER BY
Reverse(Left(LTrim(reverse(sName)), (PATINDEX('% %', LTrim(reverse(sName))) - 1)))


Now this works fine on most names. However there are some rows which have the name field left blank. In this situation
PATINDEX('% %', LTrim(reverse(sName))) evaluates to 0 and so LTrim is called with a -1 parameter. The query subsequently erros out.

I've tried running an ISNULL on the select statement to replace blank name values. Eg

SELECT ISNULL(sName, 'AAAAA')
FROM tblUsers


but the field is blank rather than null so it doesn't pick them up.

Any help on how to get round this matter would be appreciated.

Thanks

Charlie

 

[dickiebird]

......where sName != ' '
the no. of spaces should be size of column sName
HTH
;-) Dickie Bird
db@dickiebird.freeserve.co.uk

 

[chazbadd]

Cheers for the solution DickieBird,

I've actually found another way to do it.

Changing

Reverse(Left(LTrim(reverse(sName)), (PATINDEX('% %', LTrim(reverse(sName))) - 1)))

To

LTrim(Reverse(Left(LTrim(reverse(sName)), PATINDEX('% %', LTrim(reverse(sName))))))

Has the desired effect.

Thanks anyway


Charlie