Solved

Drop 2 digits only from a 6 digit in colomn in a table

Posted on 2009-04-02
22
323 Views
Last Modified: 2012-08-13
Hi,

In a table I need to delete the first 2 digits of only certain data in a colomn.

For example:

Table: dbo.123
Colomn: Employee
Data:
12 - Do not change
145 - Do not change
664841 - Needs to be changed to 4841
485691 - Needs to be changed to 5691
157289 - Needs to be changed to 7289

By the way I don't know what I'm doing here so I've backed up the Database...

Thanks,

Mike
0
Comment
Question by:huntex
  • 9
  • 6
  • 2
  • +3
22 Comments
 
LVL 12

Accepted Solution

by:
udayakumarlm earned 250 total points
Comment Utility
this will select as per your requirement(chage the 3 to any number that you want to remove)
select subString(cast(FieldName as varchar),3,len(cast(FieldName as varchar)))
from TableName
where  len(cast(FieldName as varchar))   >3

if you want to update then

update TableName
set  FieldName =subString(cast(FieldName as varchar),3,len(cast(FieldName as varchar)))
where  len(cast(FieldName as varchar))   >3





0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
I think you can use substring here
is this field defined as text or numeric?

update mytable
set myfield = substring(myfield, 3)
where len(myfield>=6

is it >= 6 size you want?

cant remember exact syntax, I need to look it up
0
 
LVL 8

Expert Comment

by:vinurajr
Comment Utility
Try this.....
declare @table table(num int)
 

insert into @table

select 12 

union all

select 145 

union all

select 664841 

union all

select 485691 

union all

select 157289
 

select right(num,4) from @table

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
I wont bother checking as it looks like first response is along those lines
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
anyways I got a missing ) on the end of len
0
 
LVL 8

Expert Comment

by:tpi007
Comment Utility
-- String Data and assuming varchar datatytpe if length of data gerater than  5 characters then reduce by 2 characters, test solution first
SELECT RIGHT(Employee,LEN(Employee) - 2)
FROM dbo.123
WHERE LEN(Employee) > 5

-- String Data and assuming varchar datatype if length of data gerater than  5 characters and reduce by 2 characters
UPDATE dbo.123
SET Employee = RIGHT(Employee,LEN(Employee) - 2)
WHERE LEN(Employee) > 5

0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Is it what you mean:


SELECT CASE WHEN LEN(RTRIM(Employee)) = 6 THEN RIGHT(Employee,4)

ELSE Employee END

FROM dbo.123

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Replace the LEN condition = 6 to match your criteria
0
 

Author Comment

by:huntex
Comment Utility
Here's what I mean, I tried the first suggestion, the second and the last but I don't really understand... :(
sql.jpg
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
huntex did you try the suggested queries?

first one should work, one I posted doesnt change numeric to string otherwise it is a similar thing.
0
 

Author Comment

by:huntex
Comment Utility
OK I ran the script (The first one), the problem is that it also changed for example 2309 to 09
I might have forgot to say that I need it to remove the first 2 numbers of 6 digit numbers only
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
so where we do the len check ensure it is just 6

where len(mynumberfield) = 6

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
or as per first post, ensure you convert to string if current field is defined as numeric

where  len(cast(mynymberfield as varchar))  = 6
0
 

Author Comment

by:huntex
Comment Utility
OK I tried this:

update dbo.CPY10150
set  PEmployeeID =subString(cast(PEmployeeID as varchar),6,len(cast(PEmployeeID as varchar)))
where  len(cast(PEmployeeID as varchar))  = 6


and I get this:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PKCPY10150'. Cannot insert duplicate key in object 'dbo.CPY10150'.
The statement has been terminated.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok, here is a question
why are you changing the primary key? you are not supposed to do that. If this key is used as a foreign key in another table then you will lose that relationship

the error you got is because the result of removing the two digits mean it is going to produce a value already in that table. And because this is a primary key, there cannot be duplicates.

If you introduce duplicates you will never be able to identify a particular row and so will have difficulties updating
0
 

Author Comment

by:huntex
Comment Utility
Emmm I didn't want to change the primary key, all I did was copy and paste the script lol
then I added what you said :
(or as per first post, ensure you convert to string if current field is defined as numeric

where  len(cast(mynymberfield as varchar))  = 6)
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
no worries
what is the exact field you want to change then?

the query here uses employeeid, is that the right field you want changing?

update dbo.CPY10150
set  PEmployeeID =subString(cast(PEmployeeID as varchar),6,len(cast(PEmployeeID as varchar)))
where  len(cast(PEmployeeID as varchar))  = 6
0
 

Author Comment

by:huntex
Comment Utility
the field I want to change is PemployeeID

When I run your script I get:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PKCPY10150'. Cannot insert duplicate key in object 'dbo.CPY10150'.
The statement has been terminated.
0
 
LVL 12

Expert Comment

by:udayakumarlm
Comment Utility
you are actually creating duplicate PemployeeID when using the substring of the existing PemployeeID
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
you are wanting to change PemployeeID as since this is a primary key, it is not good practice to do so.

so back to what I said earlier, by you changing it, you are creating a duplicate primary key. Primary keys have to be unique in order to identify the row.

What is the reasoning behind chaning this key?



0
 

Author Comment

by:huntex
Comment Utility
Thanks for your help, this is what worked:

update dbo.CPY10140
set  PEmployeeID =subString(cast(PEmployeeID as varchar),3,len(cast(PEmployeeID as varchar)))
where  len(cast(PEmployeeID as varchar))   =6
0
 
LVL 8

Expert Comment

by:vinurajr
Comment Utility
update dbo.CPY10140
set  PEmployeeID =subString(cast(PEmployeeID as varchar),3,len(cast(PEmployeeID as varchar)))
where  len(cast(PEmployeeID as varchar))   =6

This u can write as

update dbo.CPY10140
set  PEmployeeID =subString(cast(PEmployeeID as varchar),3,6)
where  len(cast(PEmployeeID as varchar))   =6

this is Because u are defining the length as 6

u can re-write the code as , any how u only need the last 4 digits...
update dbo.CPY10140 set  PEmployeeID =right(PEmployeeID ,4)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 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

12 Experts available now in Live!

Get 1:1 Help Now