SQL Query To Trim the string

Question

I have a query as follows

select 
    First_Name+' '+Middle_Name+' '+Last_Name as studentname,
    Batch,
    Batch+1 as batch1,
    Admtd_Semester,
    Program,
    Title,
    @His_Her as His_Her,
    Fathers_Name,
    Branch_Name,
    Student_Mobile_Number,
    Fathers_Mobilenumber,
    CONVERT(VARCHAR(10),GETDATE(),110) as date 
from 
    STUDENT_Admission_1212341

In the above query, batch+1 gives the output as 2014 in which I need only 14. How can I trim it?

Answer 1

SELECT First_Name + ' ' + Middle_Name + ' ' + Last_Name AS studentname
      ,Batch
      ,right(Batch,2) + 1 AS batch1
      ,Admtd_Semester
      ,Program
      ,Title
      ,@His_Her AS His_Her
      ,Fathers_Name
      ,Branch_Name
      ,Student_Mobile_Number
      ,Fathers_Mobilenumber
      ,CONVERT ( VARCHAR ( 10 ) ,GETDATE () ,110 ) AS date
FROM STUDENT_Admission_1212341;

Answer 2

Calc the remainder:

SELECT 2014 % 100

I.e.:

SELECT  First_Name + ' ' + Middle_Name + ' ' + Last_Name AS studentname ,
        Batch ,
        (Batch + 1 ) % 100 AS batch1 ,
        Admtd_Semester ,
        Program ,
        Title ,
        @His_Her AS His_Her ,
        Fathers_Name ,
        Branch_Name ,
        Student_Mobile_Number ,
        Fathers_Mobilenumber ,
        CONVERT(VARCHAR(10), GETDATE(), 110) AS date
FROM    STUDENT_Admission_1212341

Answer 3

Use the RIGHT function.

SELECT First_Name+' '+Middle_Name+' '+Last_Name AS studentname, 
Batch, 
RIGHT(Batch,2) + 1 AS batch1, 
Admtd_Semester, 
Program,Title, 
@His_Her AS His_Her, 
Fathers_Name, 
Branch_Name, 
Student_Mobile_Number, 
Fathers_Mobilenumber, CONVERT(VARCHAR(10),GETDATE(),110) AS date 
FROM STUDENT_Admission_1212341

Answer 4

Try this:

RIGHT(CAST(Batch + 1 AS VARCHAR(10), 2) AS batch1

Answer 5

select 
    First_Name+' '+Middle_Name+' '+Last_Name as studentname,
    Batch,
    RIGHT(Batch + 1, 2) as batch1,
    Admtd_Semester,
    Program,
    Title,
    @His_Her as His_Her,
    Fathers_Name,
    Branch_Name,
    Student_Mobile_Number,
    Fathers_Mobilenumber,
    CONVERT(VARCHAR(10),GETDATE(),110) as date 
from 
    STUDENT_Admission_1212341

Tests

2099 gives 99

2009 gives 09

Answer 6

Why can't you try using SubString

select 
    First_Name+' '+Middle_Name+' '+Last_Name as studentname,
    Batch,
  substring(Batch+1,3,4) as batch1,
    Admtd_Semester,
    Program,
    Title,
    @His_Her as His_Her,
    Fathers_Name,
    Branch_Name,
    Student_Mobile_Number,
    Fathers_Mobilenumber,
    CONVERT(VARCHAR(10),GETDATE(),110) as date 
from 
    STUDENT_Admission_1212341

This will probably give you the same result