Solved

SQL substring statement

Posted on 2012-03-26
11
366 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

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…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

791 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