SQLTeam.com | Weblogs | Forums

I need to get only those rows which has less than 4 characters after space

tsql
sql2008

#1

I was able to get the rows which has space and has four or more characters after space. But not sure how to get the rows has four or less characters after the space.

SELECT MASTER_CUSTOMER_ID,
LABEL_NAME,
FIRST_NAME,
MIDDLE_NAME,
LAST_NAME,
NAME_CREDENTIALS,
RECORD_TYPE,
CUSTOMER_CLASS_CODE,
CUSTOMER_STATUS_CODE
FROM CUSTOMER
WHERE (RECORD_TYPE = 'i') AND (CUSTOMER_STATUS_CODE = 'ACTIVE') AND (LAST_NAME = '% %[^ ][^ ][^ ][^ ]') AND (NOT (NAME_CREDENTIALS IS NULL)) OR
(RECORD_TYPE = 'i') AND (CUSTOMER_STATUS_CODE = 'active') AND (LAST_NAME LIKE '%.%') AND (NOT (NAME_CREDENTIALS IS NULL)) OR
(RECORD_TYPE = 'I') AND (CUSTOMER_STATUS_CODE = 'active') AND (LAST_NAME LIKE '%,%') AND (NOT (NAME_CREDENTIALS IS NULL))
ORDER BY LAST_NAME


#2

len(lastname ) -charindex(' ',lastname ) <= 4