CODEWITHSUNDEEP
sql-servert-sql
Criss Nautilus
Criss Nautilus

Reputation: 21

How to use CASE WHEN in determining special characters?

I'm trying to create a code that will do different update to a specific column based on a set of critierias.

I have a FirstName in the Customer records. Now I need to update the FirstName depending on the data I have in my title.

The logic should go like this...

UPDATE Customer
SET FirstName = If TITLE  contains special characters 
                   THEN set firstname to blank
                ELSE
                    SET it to FirstName
From Customer c
INNER JOIN CustomerList cl
    on c.customerid = cl.customerid

I'm having troubles doing the logic for the special characters. I'm not sure whether to use a CASE within a CASE or an IF and CASE statement. I am able to identify special characters using this code:

SELECT title FROM Customer where LTRIM(RTRIM(title)) NOT LIKE '%[`'' -''|-~.,-@!#$^&*()<>:;''"_+\/=?0-9]%'

I tried to incorporate this into my CASE but I'm getting a syntax error.

Any suggestions on the best way to solve this?

So far my code is like this...

SELECT ID, CASE LTRIM(RTRIM(title))
                    WHEN Title  NOT LIKE '%[`'' -''|-~.,-@!#$^&*()<>:;''"_+\/=?0-9]%' AND RTRIM(LTRIM(title)) NOT IN ('MR', 'MS', 'MRS', 'Miss', 'MSTR', 'MR.', 'MS.', 'MRS.', 'CHD', 'CHIL', 'CHLD', 'DR', 'DR.', 'MAST',''))
                    THEN title
                END

FROM customer

Upvotes: 2

Views: 2835

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44336

Assuming any character that is not a space or a letter is considered special

UPDATE c
SET FirstName = 
case when replace(Title, ' ', '') like '%[^a-z]%'
     THEN ''
     ELSE FirstName 
    end
FROM Customer c
INNER JOIN CustomerList cl
on c.customerid = cl.customerid

Upvotes: 1

Devart
Devart

Reputation: 122002

Possible this be helpful for you -

SELECT ID, 
    CASE
        WHEN LTRIM(RTRIM(title)) NOT LIKE '%[`'' -''|-~.,-@!#$^&*()<>:;''"_+\/=?0-9]%' 
            AND RTRIM(LTRIM(title)) NOT IN ('MR', 'MS', 'MRS', 'Miss', 'MSTR', 'MR.', 'MS.', 'MRS.', 'CHD', 'CHIL', 'CHLD', 'DR', 'DR.', 'MAST','')
        THEN title
    END

FROM customer

Update:

DECLARE @temp TABLE (title NVARCHAR(50))
INSERT INTO @temp (title)
VALUES ('~test1'), ('MR test'), ('is ok')

SELECT t.*
FROM @temp t
WHERE NOT EXISTS(
    SELECT 1
    FROM (VALUES
        ('MR'), ('MS'), ('MRS'), 
        ('Miss'), ('MSTR'), ('MR.'), 
        ('MS.'), ('MRS.'), ('CHD'), 
        ('CHIL'), ('CHLD'), ('DR'), 
        ('DR.'), ('MAST')
    ) c(t2) 
    WHERE LTRIM(RTRIM(title)) LIKE '%' + t2 + '%'
)
AND LTRIM(RTRIM(title)) NOT LIKE '%[`'' -''|-~.,-@!#$^&*()<>:;''"_+\/=?0-9]%'

Upvotes: 1

Related Questions