Regular Expression :: Pull 3 Or 4 Digit Numbers
I would like to pull all the model number from my products table that are strictly only a 3 or 4 digit number ie:
034 or 3542 or 0243 or 934 etc
Can someone advise me on how to construct a regular expression to give me the data I need?I have tried the following but it wasnt 100% accurate:
Code:
products_model REGEXP "[[:<:]][0-9][0-9][0-9][0-9][[:>:]]" or products_model REGEXP "[[:<:]][0-9][0-9][0-9][[:>:]]"
View Complete Forum Thread with Replies
Related Forum Messages:
Regular Expression Query
I've got a MySQL db which has unescaped ampersands which need fixing without changing any properly escaped ampersands and other entities which are present. I'm looking for a MySQL regular expression query to select only unescaped ampersands and ignore properly escaped ampersands and other entities. I found this regular expression &(?!(?i:#((x([dA-F]){1,5})|(104857[0-5]|10485[0-6]d|1048[0-4]dd|104[0-7]d{3}|10[0-3]d{4}|0?d{1,6}))|([A-Za-zd.]{2,31}));)
View Replies !
ORDER BY With Regular Expression
I would like to query a table and sort by contents of the table. For example: SELECT * FROM my_table WHERE some_id='1' ORDER BY some_field REGEXP '<pattern>(.*)</pattern>'; I know this won't work, but its the concept of what I would like to do. I've searched forums and google, and can only find information on how to use REGEXP to return a 1 or a 0. I want to re-use the (.*) pattern to determine the ORDER BY. For example if I have 2 rows that have: some_field="<pattern>ABC</pattern>" some_field="<pattern>DEF</pattern>" I would want to ASC return ABC, then DEF rows DESC would return DEF row, and the ABC row. Since I have not been able to locate any information on this, I am guessing that what I want to do can not be done. Does anyone know if this is true?
View Replies !
Regular Expression In Select Query
i am trying to query zip codes in mysql db. here is what i have: SELECT * FROM `user` WHERE zipCode REGEXP '^[A-Za-z][0-9][A-Za-z] ?[0-9][A-Za-z][0-9]$'' it gave me an error. its my first time using regex, any thoughts why i am getting this error.
View Replies !
Regular Expression :: Strip HTML
I'd like to be able to strip the HTML from a field before getting the first 50 words. I'm using SUBSTRING_INDEX(description, ' ', 50) to do get all up to the 50th space, but the spaces in the HTML tags also count. I tried using a combination of REPLACE and REGEXP, but there was no difference: SUBSTRING_INDEX(REPLACE(description, REGEXP '(<[a-zA-Z][^>]>)',''), ' ', 50) Anyone done this before? the only other result in my search was to that darned Experts Exchange.
View Replies !
Replace String With Another Using Regular Expression.
I am trying to do the following : I want to replace all the instances of dataname in db with another string. The dataname is of the form -> name{device}. I need to replace this with -> name. (i.e to remove the parentheses). I know there is a replace String functionality, but I want to have a regular expression along, which I think currently MySQL doesnt support. Can someone help me in how do I go about writing this update query, updating all the datanames with parentheses with just the name alone?
View Replies !
Regular Expression Applied To A TABLE FIELD
I searched the web but I didnt find an answer to my problem: I have a table that has a field 'code' with values like 110.003.987' OR LIKE '10 234.23.1146 234' and a user inputs in my site a value like 110003987 or 10 234.23.1146 234. How can I make the match? I wanted to use regular expressions, but I need them to be applied to the TABLE FIELD and NOT to the value I am searching. If there is another way to make the search without regular expr, there is no problem I just need this solved.
View Replies !
Two Digit Numbers Not Formatting Properly
I have a table I am using to populate drop-down menus. It is for languages. each language has a specific code that must be saved to the field in question.They work fine except for two codes: 00 and 01. I can't change the codes to anything else. They have to be 00 and 01. THey are saving to the spreadsheet as 0 and 1 however.
View Replies !
Storing Numbers In 3 Digit Format
I have a table in which one column stores 3 digit numbers. Problem is, I wanna store 35 as 035 & 3 as 003. How do I do this in MySQL? If it is not possible by having the field as INTEGER, Is it possible to convert the current values to 3 digit format in the table by changing the field type to VARCHAR? If so how?
View Replies !
Regular Expression :: Search In One Field Concatenate Result In Other Field
Here is a tough one and it might not be possible but I hate to give up without a fight: I'm trying to make a query that runs through a database and searches for a regular expression in one field, then concatenates what it matched to the end of another field. Is this possible or do I have to concatenate the whole field? Can you use regexp with replace and concat at all or is it only used in the where clause? Normally I'd use a program to hold the value it matched and then run it in a different line but for this I need to use only mySQL. It kind of implies a reversed syntax because it needs to search for the string before it updates.
View Replies !
Join After Removing The Last 2 Digit
Code: data in myTable3 (id) city (132) Boston (291) New York (569) Paris (837) London (1295) Berlin (3659) Seoul (17292) Montreal I have data in myTable3 like the above. The last two digits in red are just check digits. So the real id of Boston is 1. the real id of New York is 2. the real id of Paris is 5. the real id of London is 8. the real id of Berlin is 12. the real id of Seoul is 36. the real id of montreal is 172. I like to join myTable4 on the real id of myTable3 = myTable4.id. The following doesn't work correctly, but I hope you understand what I want by the would-be code below. Code: would-be code left join myTable4 on reverse(removeCharacter(reverse(myTable3.id),from 1, to 2))=myTable4.id I am using mySQL 5.
View Replies !
How To Sort 1/2 Digit Integers With Order By
trying to order the results from a select but I found the query choses the first digit of the integer as the index. Example, after trying "select * from A order by B I get the entries in this order: 1 10 11 12 13 2 3 4 5 Am I missing something or is this something particular from the application (HP Quality Center)?
View Replies !
Date Stored As 10 Digit Integer
I have a vendor supplied database that I am trying to query. The database stores a list of events that includes the date and time that each event takes place. The date and time, however, is stored in the database as a 10 digit integer (for example 1137015000 represents 4:30 pm January 11, 2006 Eastern Standard Time). My assumption is that the number is a count of seconds beginnning from a certain date some time in 1969 or 1970. What I am trying to do is query all events that happen on or after the current date. However when I use curdate() the format of the date is obviously different. So my question is is there a way to convert that integer to a standard date format or is there a way to convert the result of curdate() to an integer so I can compare to two values?
View Replies !
Date Created Field Shows 10 Digit Number
The database I am using seems to store its dates in a 10 digit format. For example, a table that has a date created field stores its data as a 10 digit number (1218437735). I need to be able to show these as a (proper) dates and to be able to do calculations (datediff and add/subtract) on them.
View Replies !
Pull All Records
if it were possible to write a statement in SQL where one could select several fields from a table and then limit the records returned by only displaying those where, say, FieldA (a varchar) contains a given short string which could be only a part of the field's contents. As long as a record has this short string in Field A, it should be included in the resulting set of records.Something like this: SELECT FieldA, FieldB, FieldC FROM TABLE Table1 WHERE FieldA *contains string* Is there anything in SQL that could examine the contents of a field for a partial string, and then return the records that have that partial string in that field?
View Replies !
Multiple DB Pull
Lets say there is a database of special users and a database of favorite movies SPECIAL USERS (username) bob jim FAVORITES (username - movie_title) bob - movie 1 kim - movie 3 jim - movie 1 larry - movie 2 jane - movie 2 How would I write a query that lists the most popular movies of special users without explicitly doing: SELECT * FROM FAVORITES WHERE username = 'bob' or username = 'jim' GROUP BY movie_title I want something like this: SELECT * FROM FAVORITES WHERE username = [all the usernames from the SPECIAL USERS DB] GROUP BY movie_title
View Replies !
Mysql Pull
I got this in my mysql databe table. a:1:{i:0;O:19:"test_Enclosure":4:{s:4:"link";s:36:"http://grabber/v/CAcpLiw4qt4.swf";s:4:"type";s:29:"application/x-shockwave-flash";s:6:"length";N;s:10:"javascript";s:2:"js";}} I want to pull out only the http://grabber/v/CAcpLiw4qt4.swf part?
View Replies !
Can Input Japanese, Can't Pull It Out
I figured out how to store Japanese characters by setting the collation of a particular field to utf8_bin. I can input Japanese characters, and I can see them just fine under PHPMyAdmin. The problem is that when I execute a query and print them to a web page, all I get is question marks instead of Japanese characters. This is while using the same browser that allows PHPmyAdmin to display the characters correctly. I added the following metadata to the <head> portion of the document but it didn't help: <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
View Replies !
Populate A Pull Down Menu From Db
how do I populate a pull down menu from a MySQL table? Using PHP/MySQL 4.0 Have table1 with names and ID and tableb with ID and statistical info.wnt to be able to scroll names and have users able to click on the name to follow a link to a page created using tableb data for each name listed. Table examples: tablea ID l Name --------------------- 1 Person, Joe 2 Smith, Joe tableb ID l units l margin -------------------------------- 1 5023 123 2 2536 72
View Replies !
Code To Pull Amount
I'm certain there is a function to do this but I forget what it is... Say I am pulling values from the database, but I only want to pull like the first 10 chars of the value.. how do I do that?
View Replies !
Pull Hosting Plans
I am currently creating a web hosting website for a client of mine, for the packages and the order process we are using a script called "Auto-Host (http://www.idevspot.com/AutoHost.php)", which stores all the hosting plans into a mysql database. This is a great script and it does exactly what we want it too, the only thing missing is it doesnt create a page where people can view the hosting plans and click a sign-up button. Can someone please give me guidance on how i can get a page with all the hosting plans on please?, alternatively i am willing to pay somebody for doing this on my behalf
View Replies !
Searching With Regular Expressions
I'm tweaking a small MySQL-based search engine and I'm not sure if my approach is the most efficient. Although the search is much more accurate and robust now, it has slowed down considerably. What I'm doing is the following: 1. I retrieve the user input and convert all 'OR' words to '|' (pipe chars). 2. I convert all white spaces to '.+' so that it can match words separated by other characters (i.e. "paris hilton" will match "paris hilton in spain" or "paris vacationing at the hilton", etc). 3. I query the database, for example...Code: SELECT * FROM MyTable WHERE Category REGEXP 'paris.+hilton|beach' OR Keywords REGEXP 'paris.+hilton|beach'; I would really appreciate any feedback regarding the efficiency of the REGEXP and search method I'm using, as well as any ideas related to making the search faster. The table I'm currently searching has over 80,000 records and I'm searching three columns (Filename, Category and Keywords).
View Replies !
How Do I Set Up A Regular Backup Of My Database?
I know I can do it manually in phpMyAdmin, but is there a way to set up a scheduled back-up say once a day? It would be even better if I could set up some sort of cycle where at any one time I'd have 7 versions backed up (Monday-Sunday) and each day that backup would overwrite its old version (that is, on Monday the db backup would be saved over the old Monday backup, and on Tuesday it would be saved over the Tuesday backup). Just in case something goes unnoticed for a couple days I would have the ability to jump back to a given day within a week.
View Replies !
Pull The Last 10 Records From A Transactions From A Table
I'm trying to pull the last 10 records from a transactions from a table using this query: SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10 But I want to display the rows in ascending order by timestamp. I can't get the subquery below to work and not sure why: SELECT * FROM (SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10) ORDER BY timestamp ASC;
View Replies !
Pull Related Info Using Subqueries
I'm creating a tool to browse a database of movie listings. The browser pulls up 25 results at a time, and you can page through them using 'Next' and 'Prev' tools. Pretty basic stuff. Here are my tables: movies directors comments movies_directors movies_comments etc... (primary key is movies.id) But here's where I'm stuck: for each film, I need to pull the movie info in 'movies', plus any related data from other tables, like this: The Lord of the Rings | Peter Jackson | 3 comments Episode II | George Lucas | 0 comments Indiana Jones | Steven Spielberg | 15 comments I seem to run into problems when I try to join info from all of these related tables. If there's a match, great. But, if not (like a movie with no comments), the movie is excluded from the result set. I've tried all sorts of SQL queries to make this work, but nothing seems to do the trick. SO, HERE'S MY QUESTION, IS THERE ANY WAY TO DO THIS QUERY WITHOUT SUBQUERIES/MYSQL 4.0? Or would the best approach be to use PHP to do all the subquery lookups?
View Replies !
Pull Similar Data From Two Tables
I need a query to search two seperate tables for similar data and return them in one result. I know you can do multiple select statements in one query but I dont know how to exactly organize them into one result. Does anyone know how to accomplish this.
View Replies !
Result Pull And Rating Sort
I have two databases. What I would like to happen is when I have a summary of products, I would like the query to pull the info from the product table and based on the id from that table cross reference with the reviews for that particular product and calculates all the reviews for that product into a single percentage. So when I access the assoc array from PHP I would like to see : Array(Array(['id'] => '2', ['prod_name'] => 'gummy bears', ['rating'] => '72'), Array(['id'] => '57', ['prod_name'] => 'socks', ['rating'] => '52')).
View Replies !
How To Pull Data From Multiple Tables
I have a db, with several tables in it, I have now reached to the point where I need to pull data from several tables. What is the best way to do this? Should I put a column in each table, and then insert a unique key in all of them at the time the record(row) is being created? If this is the way to do it, when I am putting the unique key in each table would I just do an insert in each of the tables?
View Replies !
Pull Data From Multiple Tables
Can you pull data from 2 tables in the same database onto the same page? What about more than 2 tables? Here is my dilema: I currently have a database with 1 table, and a ton of rows. The information is property information, and each property has multiple empty suites. As of now, I have columns for Building Name, Address, and then columns for Unit Type A (for the first unit), Rent A, Type B, Rent B, etc.... I was wondering if I would be able to put the listings we have into thier own property table, and then one bigger table of all the properties individual information. Keep in mind I need to call all tables together on my portfolio pages, so I would be connecting to quite a few tables at once. Or my other option I was thinking about was to just have 2 tables: 1 Buildings info, and 1 units info. Which of these would you do, or how would you organize this data.
View Replies !
Advanced Expression
I want to use an expression E, N times in a query: SELECT * FROM `table` WHERE SQRT(E) < E... etc E would be something like POW(`field1`/`field2`, 2) How can I use E twice without having to calculate it twice which would bloat and slow the query.
View Replies !
Automate Procedures At Regular Intervals
I wonder how I create a file that checks my database at regular intervals. I want to use it to go through a date list once a day, and if it finds "todays date" email a message attached to that date. Basicly an email reminder function that will exist on the web hotel I use.
View Replies !
Storing Regular Expressions In Database
I have a table with 100s of questions, which answers need to be validated, so I am creating another table where I store regular expressions that I will use to validate the question's answers. Are there any special precautions for this type of validation? And yes I have to use the database!
View Replies !
Regular Expressions :: Grab String
Suppose a column of data has rows that look something like this form, with string followed by floating-point in parentheses: LongName(20.3553,35.324) Is there a way I can use regular expressions in MySQL to grab just the string, and not the parentheses or the floating points/commas? Also, is there a way I can then order results by where LongName (which could be DisneyLand or North Dakota or whatever, and the string itself is not unique) occurs the most times.
View Replies !
Evaluting STRING Expression As SQL
It is possible to execute string expressions in mysql? I mean, i need to execute as SQL (in a SP) some string like this: DECLARE sql; DECLARE qid BigInt; SET sql = 'SELECT * FROM table WHERE idx = ' + qid; SOME_EXECUTE_FUNCTION(sql); The real problem is: I have a table with 2 columns, 1 Autonumeric, and 2 VarChar column. In this varchars columns i store some table names. I Need to retrieve one row (easily :)) and make a select in between this 2 tables that the varchars columns return. This must be inside a Stored Procedure. I Can't found anything in the web, it is this possible? how can i solve?
View Replies !
Expression Of Non-boolean Type
IF(SELECT COUNT(*) FROM EVENT)where EXISTS convert(varchar(10)order_id) is not null RETURN ELSE DECLARE @ RED_ID the error says a where condition is expected near where. I've run the select statement alone,without the if statement, and it is fine.
View Replies !
|
TRACKER
