Solved

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

Posted on 2009-04-02
22
331 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 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