How to split number values in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hi All,

How to split the below integer value in SQL query

Month/Year
012013
022013
122013

I am able to split 122013 as 12 2013 but am not able to split "012013" and "022013" values.

Query i used to split.

SELECT left(ID, 2) as Month , Right(ID, 4) As Years FROM table

Regards,
Abhishek

Posted 7-Apr-14 19:04pm

abhiSek99

Add a Solution

Comments

ArunRajendra 8-Apr-14 1:07am

Can you give the output you are getting when you run your query.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Damian S_ · Answer 1 · 2014-04-07T19:46:00

Solution 1

Leading zeros are dropped when you read the number as an integer value. You would need to operate on them as strings (varchar).

Posted 7-Apr-14 19:46pm

_Damian S_

Maciej Los · Answer 2 · 2014-04-07T19:47:00

Solution 2

TO split integer values, you need to treat it as a text:

SQL

SELECT LEFT(CONVERT(VARCHAR(10),ID), 2) as Month , RIGHT(CONVERT(VARCHAR(10),ID), 4) As Years
FROM table

Posted 7-Apr-14 19:47pm

Maciej Los

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 3 · 2014-04-07T20:07:00

Solution 3

Yes, when I took the column as Varchar, it is working fine.

Demo

http://sqlfiddle.com/#!6/2d445/2/0[^]

Posted 7-Apr-14 20:07pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

abhiSek99 · Answer 4 · 2014-04-07T23:10:00

Hi All,
Below is the table data

ID
122013
112013
102013
92013
82013
72013
62013
52013
42013
32013
12014

i tried with the below code but its not working

Declare @input int
select @input =ID from @tableVar
IF(COUNT(@INPUT) > 6)
BEGIN
SELECT LEFT(CONVERT(VARCHAR(10),ID), 2) AS MONTH , RIGHT(CONVERT(VARCHAR(10),ID), 4) AS YEARS FROM @TABLEVAR
END
ELSE
BEGIN
SELECT LEFT(CONVERT(VARCHAR(10),ID), 1) AS MONTH , RIGHT(CONVERT(VARCHAR(10),ID), 4) AS YEARS FROM @TABLEVAR
END

Am Getting the output as:

VB

please suggest me with better answer.