visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Mysql remove or strip off char from data in a entire column
By: Squibi U.S.A.  Date: 12/03/2003 00:00:00  English  Points: 500 Status: Answered
Quality : Excellent
I have a column named Number and in it are numbers that are 8 to 11 digts in length such as 12345678 or 12345678_78

i would like to remove all but the 7 and 8th char.

How can i do this?

thank you for your help
By: carchitect Date: 12/03/2003 05:56:00 English  Type : Comment
what answer do you want just give an example
By: Squibi Date: 12/03/2003 06:02:00 English  Type : Comment
I would like to know the syntax to input into the mysql prompt that will go through my Number column and remove everything but the 7th and 8th number.

Comment from carchitect
what answer do you want just give an example?
I want a anwser i can use to acheive my goal of editing the column to only keep the 7th and 8th char.
By: carchitect Date: 12/03/2003 06:15:00 English  Type : Comment
select SUBSTRING('Quadratically',5,6);
-> 'ratica'
use this
By: Squibi Date: 12/03/2003 06:26:00 English  Type : Comment
I have read that in the mysql web page but this doesn't anwser my question. First off i want to do this to a whole column and second i don't know what the exact data is in the column just that i want the 7,8 th char.
By: VGR Date: 12/03/2003 19:40:00 English  Type : Comment
you want the 7th and 8th chars ?

Use what has been suggested to you :
SELECT SUBSTRING(Number,7,2) as a from yourtablename;
By: Squibi Date: 12/03/2003 20:02:00 English  Type : Comment
ok so your saying if my table is named photo and the column is named Number and i don't know what numbers are in the Number column but i know they are at least 10 numbers long that i can type

SELECT SUBSTRING(Number,7,2)from photo;

this is going to go into the column and alter the data so that it is just the 7th and 8th numbers all the way down the entire column.

I guess i am just stuipid or something cause that don't work.
By: TheFalklands Date: 12/03/2003 20:11:00 English  Type : Comment
You didn't mention what format the table column was created in, but it sounds like a decimal field as in...

Number DECIMAL(11,2)

Since an underscore is usually not a numeric character, I'm guessing that it is really the decimal point with two digits after the decimal possible, as in a currency field. The problem is that the length of the text in the field depends upon the actual value of the number, so if the numeric value is only a few digits, then the decimal point will be farther to the left.

So, you apply query functions to search for the decimal point and select only the two digits to the left, like...

select substring(Number,instr(Number,'.')-2,2) as Number from photo

If you want to replace the old value with the new shortened value, apply this formula in an update SQL command, like this

update photo set Number=substring(Number,instr(Number,'.')-2,2)

If your decimal is a different character, just replace it in the command above. See if that works...

TFL
By: Squibi Date: 12/03/2003 20:37:00 English  Type : Comment
sorry about the confusion my feild is a varchar(20)
By: Squibi Date: 12/03/2003 20:40:00 English  Type : Comment
and the field contains underscores sometimes. so the format is always 8 numbers and sometimes (_23) underscore 2 numbers. All i care about and all i want in the column is the 7th and 8th char.

very sorry about all the confusion here this is my first time posting here.
By: VGR Date: 12/03/2003 20:46:00 English  Type : Answer
yes, SELECT SUBSTRING(Number,7,2) from photo; does select the 7th and 8th characters from the field Number in the table photo.
To change the column, do :
UPDATE photo set Number=SUBSTRING(Number,7,2);
By: TheFalklands Date: 12/03/2003 20:46:00 English  Type : Comment
Okay, if it is always at least the first 8 characters, then issue the following update command:

update photo set Number=substring(Number,7,2)

That should work, since you have at least the first 8 characters.

TFL
By: VGR Date: 12/03/2003 20:53:00 English  Type : Comment
too late young Padawan :D
By: Squibi Date: 12/03/2003 20:55:00 English  Type : Comment
THANK YOU SO MUCH VGR & THEFALKLANDS this is excactly what I needed. Is there anyway for me to divide the points up 250 each for this question because you both have done a termendous job at the exact same time!
By: Squibi Date: 12/03/2003 20:56:00 English  Type : Comment
yes vgr you were first but thank you to both of you for helping!!!!
By: TheFalklands Date: 12/03/2003 20:59:00 English  Type : Comment
It was my pleasure. It's my fault for not submitting a minute sooner. VGR was on the right track anyway.

TFL
By: VGR Date: 12/03/2003 21:24:00 English  Type : Comment
I didn't understand that he (Squibi) did not deduce the UPDATE syntax from the results of the SELECT provided 8-)
Sorry, I'll know for next time :D
Yes, you can "divide" points by :
-asking in feedback area to reduce this question's points (may-be you can do it yourself, I don't know, I almost never asked any questions)
-granting the good answer to one of the two (well, it's done, thanks)
-posting a new question named "points for ..." for the second one you want to retribute
-he/she'll then post a comment, you accept it, and voilà.
By: Squibi Date: 12/03/2003 21:51:00 English  Type : Comment
can I add a order statement to the end of that to order by
number?
By: VGR Date: 12/03/2003 21:59:00 English  Type : Comment
OF COURSE you can IN A SELECT (not in an UPDATE :D )
but then write SELECT SUBSTRING(Number,7,2) as a from photo ORDER BY a ASC;

if you want to reorder physically the lines based on this order, sorry but it's heresy
By: carchitect Date: 12/03/2003 22:42:00 English  Type : Comment
thats really bad
i think i gave you answer lot earlier....
By: VGR Date: 13/03/2003 07:26:00 English  Type : Comment
yes carchitect, you sent an answer (like my first ones) that supposed the user could build (or "see") the solution from what you wrote.
You're 100% right, what you wrote (a copy-paste of the MySql DOC by the way ;-) if the same as my "Use what has been suggested to you :
SELECT SUBSTRING(Number,7,2) as a from yourtablename; "
(note I said that it had been suggested :D )(by you 8-)

Alas, he wanted the exact UPDATE syntax. Thus our answers did not meet his expectations.

I give you my points if you want.
By: carchitect Date: 13/03/2003 07:41:00 English  Type : Comment
no VGR
No
i would never like to get points like this
don't mind that was just a comment....
By: VGR Date: 13/03/2003 07:51:00 English  Type : Comment
my pleasure is your pleasure
just tell
By: carchitect Date: 13/03/2003 20:02:00 English  Type : Comment
o yes ofcourse VGR i always respect you, you are lot more senior to me...
By: VGR Date: 13/03/2003 20:07:00 English  Type : Comment
boarf 8-)
"forever young, we are forever young..."
By: VGR Date: 13/03/2003 20:08:00 English  Type : Comment
sorry : "forever young, we wanna be forever young"
By: carchitect Date: 13/03/2003 20:30:00 English  Type : Comment
ha ha you will remain young my dear friend

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 367.899890 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page