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.

CodePudding user response：

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