Search code examples
sqlsubstr

sub string using if else condition in sql

I have a field of data which consists of ndc(field name) like this

00037002207 EN,
64370042140 CR,
NOT APPLICABLE,
64370042140 CR,

i want to select the ndc if it's start with %NOT APP% i have to get substr(ndc,0,14) if it's starts with numaric substr(ndc,0,11). as i am new to sql i am breaking my head appricate for suggestions.

Solution

  • Assuming you literally only have these two possible kinds of values and they are always presented exactly the same way, and you actually want the entire numeric portion where there is one, and the entire "NOT APPLICABLE" value when not:

    declare @inputData table (val varchar(max))
insert into @inputData values ('00037002207 EN'),('64370042140 CR'),('NOT APPLICABLE'),('64370042140 CR')

select
    val,
    case 
        when val like 'NOT APP%'
        then substring(val,0,15)
        else substring(val,0,12)
    end as outputVal
from @inputData
    val outputVal
    00037002207 EN 00037002207
    64370042140 CR 64370042140
    NOT APPLICABLE NOT APPLICABLE
    64370042140 CR 64370042140