?
Solved

SQL substring statement

Posted on 2012-03-26
11
Medium Priority
?
371 Views
Last Modified: 2012-04-12
What I need to do is to do an UPDATE SQL statement where I add one to the first 4 digits of a field, the year, if the last 2 digits of the field, the month, is greater than 12.  I can't figure out how to do this.  This is what I have right now but it doesn't work.

UPDATE table1 SET SUBSTRING(PeriodStart, 1, 4) = SUBSTRING(PeriodStart, 1, 4) + 1 WHERE SUBSTRING(PeriodStart, 5, 2) > 12
0
Comment
Question by:zintech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 13

Expert Comment

by:Ashok
ID: 37767525
What is Data Type for PeriodStart?
0
 

Author Comment

by:zintech
ID: 37767527
It is integer data type
0
 
LVL 7

Expert Comment

by:designatedinitializer
ID: 37767559
I'm sorry to break it to you like this, but you're doing it all wrong.
You should store dates as DATE datatype. And then you can use MONTH(), DAY()... and a whole bunch of other date functions to do whatever date computations you need.
0
Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

 
LVL 13

Expert Comment

by:Ashok
ID: 37767591
BTW, you cannot use SubString directly on integer.....

select * from Table1
where
CAST(Substring(CAST(PeriodStart as varchar(100)), 5,2) AS INT)  > 12
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37767618
Just as designatedinitializer has mentioned,
you should be using Date as Data Type for this field.

Ashok
0
 
LVL 9

Expert Comment

by:OCDan
ID: 37767630
As said using a date field would be a lot better but below should help.

You don't need substring to do what you need try this:

update table1
set periodstart = convert(int,convert(varchar,left(periodstart,4)+1) + convert(varchar,right(periodstart,2)))
where left(periodstart,4)>12

Open in new window

0
 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 37767634
I agree that you should probably store the dates as dates... but if you are storing them as integers, you should just be able to add a number to the value.  Substring will not work with the integer data type.  It looks like you are trying to cycle through the year, and add the one to the year value when the month value exceeds 12.  Is this correct.

If your date was stored like 20110102 for 01-02-2011  then you should be able to add 10000 to the integer to increase the year value.  Obviously the number would need to be adjusted to whatever format you are using.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 37768423
List 5 input records and expected corresponding output.
0
 
LVL 9

Expert Comment

by:OCDan
ID: 37768509
Sorry my SQL should have been this instead:
update table1
set periodstart = convert(int,convert(varchar,left(periodstart,4)+1) + convert(varchar,right(periodstart,2)))
where right(periodstart,2)>12

Open in new window

0
 
LVL 30

Expert Comment

by:hnasr
ID: 37768586
Try this for table a and field periodstart.

UPDATE a
      SET periodstart =
            case WHEN substring(cast(periodstart as nvarchar(8)),7,2) > '12' THEN periodstart + 10000
            Else periodstart
END
0
 
LVL 9

Accepted Solution

by:
keyu earned 2000 total points
ID: 37770961
UPDATE table1 SET PeriodStart = convert(varchar,(convert(left(periodstart,4),int) + 1))+right(periodstart,length(periodstart)-4) WHERE right(periodstart,2)>12

DEscription:

* convert(varchar,(convert(left(periodstart,4),int) + 1)) =>

-> It will retrieve first 4 characters convert it into int
-> add 1 to this
->  again covert it back to varchar after addition

 *right(periodstart,length(periodstart)-4)

- > this will find remaining charaters of field    "periodstart"

* after this we are concating both sstring with '+' character
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question