Solved

SQL substring statement

Posted on 2012-03-26
11
369 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 500 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Removing SCCM 2016 4 42
Using rowversion for incremental load of datawarehouse. 12 30
Moving away from Access 2003 adp files 4 41
Use SSRS to email customers? 4 21
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

740 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