[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2009-04-02
22
Medium Priority
?
336 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 750 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

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 …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

650 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