Solved

SQL substring statement

Posted on 2012-03-26
11
363 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now