CODEWITHSUNDEEP
sqlsql-server
mHelpMe
mHelpMe

Reputation: 6668

query and formatting string at the same time

I am using SQL Server 2012.

I have a simple query,

select * from P_Bld
where datep = (select max(datep) from P_Bld)
and codeBlood like 'nm_%'
order by codeBlood

This works fine and returns the data as shown below

codeBlood     num
nm_aab        1
nm_abc        2
nm_rst        3

I was wondering if it is possible to change the codeBlood column so it does not include the 'nm_' and also make the rest of the string uppercase so I would get something like below,

codeBlood     num
AAB        1
ABC        2
RST        3

Is this possible?

Upvotes: 0

Views: 30

Answers (3)

AK47
AK47

Reputation: 3797

I think this will do, First replace "nm_" by blank using Replace() function & then convert remaining string to Uppercase bu Upper() function

select Upper(Replace(codeBlood,'nm_','')) as codeBlood,num from P_Bld
where datep = (select max(datep) from P_Bld)
and codeBlood like 'nm_%'
order by codeBlood

Upvotes: 3

Hossam Barakat
Hossam Barakat

Reputation: 1397

Yes you can do it like the following

select SUBSTRING ( UPPER(codeBlood) ,4 , LEN(codeBlood) ),num from P_Bld
where datep = (select max(datep) from P_Bld)
and codeBlood like 'rf_%'
order by codeBlood

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

If the pattern is same, try this

select Upper(substring(codeBlood,4,length(CodeBllod))),num from P_Bld
where datep = (select max(datep) from P_Bld)
and codeBlood like 'rf_%'
order by codeBlood

Upvotes: 1

Related Questions