• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

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

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
huntex
Asked:
huntex
  • 9
  • 6
  • 2
  • +3
1 Solution
 
udaya kumar laligondlaTechnical LeadCommented:
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
 
rockiroadsCommented:
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
 
vinurajrCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
rockiroadsCommented:
I wont bother checking as it looks like first response is along those lines
0
 
rockiroadsCommented:
anyways I got a missing ) on the end of len
0
 
tpi007Commented:
-- 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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Replace the LEN condition = 6 to match your criteria
0
 
huntexNetwork AdministratorAuthor Commented:
Here's what I mean, I tried the first suggestion, the second and the last but I don't really understand... :(
sql.jpg
0
 
rockiroadsCommented:
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
 
huntexNetwork AdministratorAuthor Commented:
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
 
rockiroadsCommented:
so where we do the len check ensure it is just 6

where len(mynumberfield) = 6

0
 
rockiroadsCommented:
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
 
huntexNetwork AdministratorAuthor Commented:
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
 
rockiroadsCommented:
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
 
huntexNetwork AdministratorAuthor Commented:
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
 
rockiroadsCommented:
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
 
huntexNetwork AdministratorAuthor Commented:
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
 
udaya kumar laligondlaTechnical LeadCommented:
you are actually creating duplicate PemployeeID when using the substring of the existing PemployeeID
0
 
rockiroadsCommented:
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
 
huntexNetwork AdministratorAuthor Commented:
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
 
vinurajrCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 9
  • 6
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now