Solved

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

Posted on 2009-04-02
22
330 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:
udaya kumar laligondla earned 250 total points
ID: 24049456
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
ID: 24049464
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
ID: 24049466
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 65

Expert Comment

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

Expert Comment

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

Expert Comment

by:tpi007
ID: 24049516
-- 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
ID: 24049560
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
ID: 24049635
Replace the LEN condition = 6 to match your criteria
0
 

Author Comment

by:huntex
ID: 24049719
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
ID: 24049776
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
ID: 24050084
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24050240
so where we do the len check ensure it is just 6

where len(mynumberfield) = 6

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24050247
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
ID: 24050458
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
ID: 24050634
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
ID: 24050662
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
ID: 24050761
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
ID: 24051374
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:udaya kumar laligondla
ID: 24051468
you are actually creating duplicate PemployeeID when using the substring of the existing PemployeeID
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24051855
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
ID: 24053540
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
ID: 24056545
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

821 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