CODEWITHSUNDEEP
sql-servert-sqlcasting
Michael
Michael

Reputation: 2657

T SQL CAST a CASE Statement

Using SQL Server 2014. How do I CAST the result of a CASE statement? I need to CAST to nvarchar(20)

This is my code:

SELECT CASE 
        WHEN [AddressLine1] LIKE 'ABC%'
            THEN REPLACE([AddressLine1], 'ABC', '')
        WHEN [AddressLine1] LIKE 'SCHEME%'
            THEN REPLACE([AddressLine1], 'QWE,', '')
        WHEN [AddressLine1] LIKE 'SBLOCK%'
            THEN REPLACE([AddressLine1], 'QWE', '')
        WHEN [AddressLine1] LIKE 'QAZ%'
            THEN REPLACE([AddressLine1], 'QAZ', '')
        ELSE LTRIM(RTRIM([AddressLine1]))
        END AS cast(search_name AS nvarchar(20)) as search_name
from myTable;

But I keep receiving the error:

Incorrect syntax near 'search_name'.

Upvotes: 1

Views: 224

Answers (2)

SQLSam
SQLSam

Reputation: 537

You need to cast around the whole result of the case.

SELECT cast(CASE 
        WHEN [AddressLine1] LIKE 'ABC%'
            THEN REPLACE([AddressLine1], 'ABC', '')
        WHEN [AddressLine1] LIKE 'SCHEME%'
            THEN REPLACE([AddressLine1], 'QWE,', '')
        WHEN [AddressLine1] LIKE 'SBLOCK%'
            THEN REPLACE([AddressLine1], 'QWE', '')
        WHEN [AddressLine1] LIKE 'QAZ%'
            THEN REPLACE([AddressLine1], 'QAZ', '')
        ELSE LTRIM(RTRIM([AddressLine1]))
        END as nvarchar(20))  AS search_name
from myTable;

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need to cast the whole case expression to do conversation :

If you want search_name as searchable then you can use apply : 

SELECT t.*, CAST(search_name AS NVARCHAR(20)) AS search_name 
FROM table t CROSS APPLY
     ( VALUES (CASE WHEN [AddressLine1] LIKE 'ABC%'
                    THEN REPLACE([AddressLine1], 'ABC', '')
                    WHEN [AddressLine1] LIKE 'SCHEME%'
                    THEN REPLACE([AddressLine1], 'QWE,', '')
                    WHEN [AddressLine1] LIKE 'SBLOCK%'
                    THEN REPLACE([AddressLine1], 'QWE', '')
                    WHEN [AddressLine1] LIKE 'QAZ%'
                    THEN REPLACE([AddressLine1], 'QAZ', '')
                    ELSE LTRIM([AddressLine1]) -- removed RTRIM()  
              END)
     ) tt(search_name);

By this way, you can search by search_name easily don't need to use sub-query or avoid repetition of case expression.

Note : I have removed RTRIM() as SQL Server will ignore right spaces while comparing the strings.

Upvotes: 0

Related Questions