Substr Return From Select
Can I do this? Can't find any ref.
select substr(col1,1,3) from table ...
select col1[1,3] from table
The column contains ABC123, DEF456, etc
I want:
ABC
DEF
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Substr Vs Substring
In my mysql database table called CLIENT, I have a phone number field. When I try to format the phone number field in my query... SELECT substr( phone, 4, 3 ) FROM client I get the error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( phone, 4, 3 ) But if I use SELECT substring( phone, 4, 3 ) FROM client The query is successful. Why cant I use the SUBSTR function? My Phone definition is varchar(10)
SUBSTR() Function
SELECT SUBSTR(message, 0, 20) FROM posts My goal is to start at the first characters on the far left, and then return 20 chars only. This function for some crazy reason seems to start at the end of the string though, which is stupid, because how do I know where the string starts?
How To Do A Substr As Part Of A Query
I want to select from a table all records where the name field starts with a particular letter such as "a" or "b" and so on... but i cant seem to get the where statement to co-operate with me. this is being done in php (if that matters) and on a linux server. the current part of the query that i am trying (or should i say the last one i tried) is" AND substr(b_name,0,1) = '" . $alpha . "' AND ";
Select To Return T/f
I'm volunteering to work on a project. I have a select statement that looks like this: SELECT id, lname, fname, team FROM attenders WHERE lname LIKE '%ha' This will show the rows whose last names begin with "ha." I also have a blob column, "pic," that contains the binary data of the person's photo. I'd like the select statement to return a 5th true value if the pic column has data in it. Or, return false if it doesn't. So it'd be something like this: SELECT id, lname, fname, team, [if pic has data] FROM attenders WHERE lname LIKE '%ha'
Return In Select
I want do make something like this: select ta1.* from table1 ta1, table2 ta2 where ta1.field=ta2.field and ta1.field=12 I want to return values even if ta2.field is null! In SQL Server its (*=): select ta1.* from table1 ta1, table2 ta2 where ta1.field*=ta2.field and ta1.field=12 How can I do this in MySQL
Many To Many Select Using DISTINCT Or Not Return The Same Results
I was on MySQL website looking for an answer, but still need assistance please. I want to do the opposite of the following: SELECT * FROM myTable WHERE id IN(1,2,3,4,5) ; I want to use my array(1,2,3,4,5) to exclude items in the result set, not include them. So, I want to do something like this: SELECT * FROM myTable WHERE id NOT_IN(1,2,3,4,5) ;
USE SELECT SUM(.....) ;WILL RETURN BLOB TYPE
i use mysql5.0.4 and Connector/NET to write asp.net project;WHEN I use "SELECT SUM(.....) as a",I found it will return blob field type;but in mysql4.1.11 will return double type ; can help me to convert blob to double
Select Group_concat Doesn't Return Numbers?!?
Say that I have a table with a with of integers and a row with text. For starters, I just want to have the integers returned in one string: select group_concat(MyIntegers) from MyTable; This gives me a lot of questionmarks: ????????? However, if I do this, I get the real result: select lower(group_concat(MyIntegers)) from MyTable; This gives me: 11,15,20,etc. If I select the string row, no extra work is needed. select group_concat(MyStrings) from MyTable; I get: "blabla,hey,there" etc. Why can't I get the numbers "as is" in the first query? I'm using the latest 5.0.19 Win32 MySQL server, running the latest ODBC driver etc. on a Windows server 2003.
Can SELECT Return The Position Of The Results?
I have a table with values: Row: Name Row: ID John 4890 Mark 4922 Peter 4934 John 4980 Joe 5029 Lisa 5044 etc... What I need is a query that I would do for example on John (name='John') that would return the following: location 1 4 In fact the only thing I need is the location of the John in the resulting table. Apart from the Name column I do have other column of ID's. They are incremental but not step 1 if this can help in any way. Is something like that possible with one query?
SELECT Count(xxx) AS Xxx Does Not Return Integer
i'm using the latest downloaded version of MySQL and am currently producing an app that is compatible with MySQL, MS Access and SQL 2000/2005, i've not really come across any problems making the app run across the 3 platforms (except for Access's wierd date syntax and MySQL's lack of a boolean field) but the followin is doing my head in: If i run a count query such as SELECT Count(CategoryID) AS CountCats FROM MyDownloads etc etc CountCat = (Downloads.Fields.Item("CountCats").Value) And then run an IF statement in my page code such as: <% If CountCat = 0 Then .... %> This works fine in Access and SQL as the Count value is seen as an integer, but in MySQL this chucks an error unless the code reads <% If CountCat = "0" Then .... %> Why is the count value not returned as an integer????? I cant understand that at all, what else could it be? I've seen another post on the forum along the same lines but with no answer to it. This is grim as every page that uses a count needs to have an additional IF statement put on it to change the second IF statement if the database is detected as MySQL.
Return Zero Instead Of Nothing
I want this query to return a zero on the days of selection that have no record. SELECT thedate, thecount FROM thetable WHERE DATE_SUB('$Today',INTERVAL 6 DAY) <= thedate AND thedate <= '$Today' && ID = $ID ORDER BY thedate ASC So it works great if all the days in the selection have a value. If one of the days in the sequence has no record, then it skips it, and thus no value is presented. The question is, how do I get mysql to give me a zero if it doesnt find anything?
Return A Value
$title = mysql_query("SELECT prod_title FROM products WHERE prod_id = '$array['spec_prod_id']'"); will $title equal a value or just that string?
Return Other Db Value
SELECT `id` FROM `users` WHERE `username` = (the username that user is logged in as) 2 things here, 1. $_SESSION['username'] can be called (in php, i don't know about mysql i would guess not) to get the username you are logged in as 2. i want the result of my query to be set a a variable such as $id
Return A Row
How can I return a row when the condition is not met for example simplified SELECT Name,SUM(Cost) AS T FROM DB WHERE Name="+Name +" GROUP BY Name this works fine but I ned it to return Name and Zero Cost if Name does not exist
How Do I Return The First Record In DB?
Table name: Fabrics fabric_id fabric_name fabric_color I would like to retrieve only the first record in the database i.e. the record with the smallest fabric_id number. I thought I could do a SQL statement like the following, but it doesn't work. SELECT MIN(fabric_id) as id, fabric_name, fabric_color FROM fabrics
From_unixtime Doesn't Return GMT
As the title says I've just come to realize the from_unixtime function returns the server's local time not GMT . I know I can probably do it manually i.e. add the offset number to the timestamp in the query.
How To Return The Timespans?
I have a table the contains the following columns empid, transtime If the count(transtime) mod 2 = 0 then I'd like to find the timediff between the first and the second transaction, the third and the fourth, the fifth and sixth and so on per employee? How can I return all the timediff in a single sql statement, if it can be done?
Why Does This Count Return 1 Instead Of 0?
I don't understand why this count will return 1, when it should return 0. If I enter the resource_id 1, it will return 2, which is correct. But if I enter any other resource_id, all of which do not exist in the 3 rows of data I have, it returns 1....
Return The Row Number
I have a problem. I need to rank data from highest to lowest, then return the value of what rank a certain piece of data is For example id value 1 20 2 80 3 60 4 120 5 10 So I need to make a query that organizes this by value 4 120 2 80 3 60 1 20 5 10 Then if my query says "for ID 1" it will return the value "4" since it is ranked 4th. Is there any easy way to do this with a query? I am using PHP as my programming language, if theres a way of doing it with PHP can someone direct me to it?
Carriage Return
I am runnin a utility to parse and import XLM files into a Mysql database on Windows however, the problem i am facing is that in some of the tables i have notice that on certain fields there is a training carriage return character. The same utility run on Linux + Mysql doesn't have that problem - running the same utility using MS Sql Server also doesn't have that problem. I am using windows 2000 professional and Mysql version 5.0.27
Return Max Field
I need to compare multiple fields and return the field that has the max value for that row. e.g. SELECT HIGHEST(largest_classroom, largest_bedroom, largest_livingroom) AS largest_room (Similar to the MAX() function but horizontal instead of vertical)
Return Value Of Functions.
OS: Windows XP Professional SP2 MySQL: 5.0.24a PHP: 5.2 RC5 PHP mysqli extension: 5.0.22 Does my MySQL function support returning of multi-row, multi-column values like a table of values instead of just scalar ones? I noticed and coded some MSSQL2005 and Postgre functions that could do the same thing. After going through the MySQL documentation, I do not see such a feature in it syntax. Maybe I overlooked that MySQL do have this feature but I do not know how to implement it.
How To Return Records Set From SP (SF) In V5.0?
i have stored procedure (or function) and I need to return records set from it. This result set is generated inside SP, so simple select <some columns> from <somewhere> will not work. In postgresql this is done using SET OF construction, but how to do it in MySQL?
Date Return
I need to be able to search a table (Record) and return the last entry for each EquipID using the Date that the entry was entered. There will be multiple entries for each EquipID I just need to return the last one entered using EqDate.
Group By To Return 1 Row.
Is it at all possible to group by and have it return all variables from a single row based on a max() criteria of a single variable? For instance in SPSS I can sort by id number descending then in an aggregate have it say that newvar2=first(newvar) or last(newvar).?? Just a thought?? any ideas?
Can I Return An ErrorID?
I am trying to find out if there is a way of returning an ErrorID after I have executed some SQL to ensure that it has worked? In SQL Server it would be: Select @@Error Is there a MySQL equivalent? The reason being that I need to execute a couple of statements but need to ensure they have worked before moving onto the next. I have read that if SQLSTATE is '00000' then it has worked but can I evaluate SQLSTATE in some way? Or store it in a variable?
How To Return A Row Number?
Using MySQL 5.0 CF8 DW CS3 I guess as usual this is a really simple question requiring a really simple answer but my head has a black hole at the moment so if someone could help me out I would be greatful I have a query: <cfquery name="rstrdt" datasource="cdsl"> SELECT * FROM players ORDER BY winpercentage DESC,played DESC,banktotal DESC </cfquery> the table the query creates has many colums but 2 of the columns are 'player' & 'club thus my question is: how would I return the row number where the player and his club are in the table, the primary key 'playerindex' identifies the player uniquely so that is of no use to me in this instance.
INSERT Return Value
if I have a table with an auto incrementing primary key, is there a way to have an INSERT into that table return the primary key value for the new row? Right now I'm doing it with two queries, one to insert the record, and a SELECT to pull the new key, and I'm hoping there's a single step process to accomplish the same thing.
Return Identity
Is there any method in MySQL v4.0... that I can return the identity of a newly inserted record? I would normally use a stored procedure in MySQL v5.0 or in SQL-Server, but MySQL v4.0 doesn't support stored procedures. Can I do it in inline sql?
Return Values
SELECT someNumber, COUNT(someSubject) FROM someTable WHERE (someNumber BETWEEN 10 AND 20) GROUP BY someNumber The query returns the count for someSubject, grouped by someNumber providing at least one matching someSubject was found for each someNumber. Where there were no matching someSubjects, the someNumber group is not returned. Specifically, I am getting back the count for 16, 17, 18, 19 and 20. Although the count for 10, 11, 12, 13 and 14 is zero, I still want this to display in the returned table. Can any help to show me how I can force the BETWEEN values to display when the count for that value is zero?
Reading Return Value
In my recordset I'm returning a value called GameDay which is equal to either 1, 2, 3, 4 or 5. When looping through the recordset, depending on the value of GameDay, I want to display a certain day when it changes. However, it appears when I'm reading the value it does not work. I tried settype and I tried reading the case values inside single quotes and I tried comparing GameDay with $GameDay in the if statement. What am I doing wrong? Thanks CODEswitch($ReturnRow1["GameDay"): case 1: $gameDay = "Thursday"; case 2: $gameDay = "Friday"; case 3; $gameDay = "Saturday"; case 4: $gameDay = "Sunday"; case 5: $gameDay = "Monday"; default: $gameDay = "Does not work"; endswitch; if($gameDay != $previousDay); { echo $gameDay; $previousDay = $gameDay; }
Return Field Name With Value
The following query returns all of the defined values within a record: SELECT * FROM sr_main WHERE symreq = '$sr_num'; Each record may have a different quantity of defined values - some may have 3 values another may have 10, etc. This means that it is difficult to know which value relates to which field. Is it possible to return not only the array of defined values but also the associated Field names?
Return All Values
On my user registration processing page I need to test the visitor's proposed login name against all registered login names to ensure uniqueness. My two MySQL books are unclear, so I have a couple of questions: 1) How do I compose a query that returns all the values in a single column (username, in this case), so that I can test my new username against them to ensure uniqueness before I write the new username to the db? 2) Does MySQL provide a quick and dirty way to do such a thing, or will I manually loop through all returned values to test for uniqueness?
Return Result Set In SPs
how can we have a select satement in an SP?(i want to return a result set from the SP). i want to migrate from SQL Server to MySQL and i don't know what to do with these SPs. it can be done in MySQL client, but it gives the error:"procedure foo can't return a result set in the given context MySQL" in the application. can i set a cursor out put parameter and return it?(just like Oracle) or it has another solution? and another question: is MyODBC 3.51 tested with MySQL 5.x? can we call an SP from Delphi via MyODBC? (i tried to do so (using ADO an a data provider for OLEDB) but i think somewhere in passing the call statement it had put {} around "call sp()" statement so that mysql returned an error about the syntax.)
Return Unique
I have a table with unique words. I want to insert new words, but if the word I'm inserting already exists I want the ID of that record returned so I can use it in the next query...
Return Dates
I need to query the database to find dates and time slots that are not in the database, so that i can find what days and times I have available for my job booking script. I have a 'DATE' (MYSQL DATE FORMAT) column and a column named 'TIME' that gets a number either 1 - 5 this defines time slots i later use php to output the time to a readable format.( for ordering reasons) My script is a scheduling and booking system, I just need an easier way to find what I have available rather than outputting the jobs in the database and finding gaps by human deduction.
How To Return Text
In SQL Server, I can put PRINT statements in the body of Stored Procedures. When executing them in Query Analyser I can view the messages sort of like debugging. In some case a PRINT statement can be the formation of error message returned to client although RAISERROR is the preferred method.Does MySQL have these capabilities also?
Return Rows
Code: select field, count(field) as count from tbl group by field How do I return only those records that have a count of 5 or greater?
Return Records
I am having trouble figuring out the WHERE syntax for a query where I want to return only the records where the data in column "user_ID" from table user_name is not found in the column "user_ID" from table user_password. So... Code: SELECT user_login_password.user_ID, user_name.user_ID FROM user_name, user_login_password How do I set up my WHERE statement so that if the same data is in both "user_name.user_ID" and "user_login_password.user_ID" the record is not returned? (or, to say it in another way, if there is no matching data, the record IS returned?)
Return Result Set
I was looking into returning a result set from a MYSQL 5.0 stored procedure to php. Unfortunately I do not know what type to return nor can I find any types that would allow me to do this. Is this possible under the 5.0 implementation?
Return Result Set In SPs
how can we have a select satement in an SP?(i want to return a result set from the SP). i want to migrate from SQL Server to MySQL and i don’t know what to do with these SPs. it can be done in MySQL client, but it gives the error:"procedure foo can’t return a result set in the given context MySQL" in the application. can i set a cursor out put parameter and return it?(just like Oracle) or it has another solution? and another question: is MyODBC 3.51 tested with MySQL 5.x? can we call an SP from Delphi via MyODBC? (i tried to do so (using ADO an a data provider for OLEDB) but i think somewhere in passing the call statement it had put {} around "call sp()" statement so that mysql returned an error about the syntax.)
Timestamp Does NOW() Return
I am inserting data into a mysql database via PHP. In my insert statement, I use NOW() to insert the current time into a field of type timestamp(14). Now I am trying to convert this timestamp to a readable date. Is this a unix timestamp? a mysql timestamp? something else I've never heard of? I tried some PHP functions I found on the web to convert a mysql timestamp to a unix timestamp. But when I plugged in the timestamp I'm getting from my data, it returns a date in the future. So either: a) the function is flawed b) the clock on the database server is off c) it's not a mysql timestamp
Return Index
If I'm inserting a row into a table, is it possible for mySQL to return to me the index that was generated by that insert?
Insert Record And Return Id
lets say i have my db setup as such user id | name | email | default_pic picture id | user_id | name if each user can have multiple pictures with one being their default it would be helpful to insert their account and default picture into the database and update their pic. right now im doing something like this //insert user into db //insert picture into dabase //select most recent picture //update default_pic with most recent picture is there any way to do this //insert picture into db and return the id just created //insert user into database with pics id we just got running these 4 sql commands as shown above seems to be slowing my page down a great deal.
Query To Return Two Count()s
I'm trying to figure out a better way of doing this: SELECT (SELECT COUNT(*) FROM GuestEvents WHERE EventID = 1 AND EventStatus = 'y' AND UserID = 'bob') AS Event1, (SELECT COUNT(*) FROM GuestEvents WHERE EventID = 2 AND EventStatus = 'y' AND UserID = 'bob') AS Event2 As you can see it's quite repetitive, I wondered if there isn't something like this which could optimise it a bit: SELECT COUNT(EventID=1) AS Event1, COUNT(EventID=2) AS Event2 FROM GuestEvents WHERE EventStatus = 'y' AND UserID = 'bob'
Greatest Val In Row But Return Field Name
I would like to find the greatest value in a row but return the column name that it is in. So for example... A B C D E -------------------- 1 5 19 33 11 I would want to return D (because 33 is the largest val). Any way to do this?
|
TRACKER
