MYSQL :: Substring Function

Substring
I have a query that works fine for my initial database population, but I need another one for after that point. Basically, I have a field that comes out as DOMAIN1+username but all I want is the user name. So I came up with this mysql statement that works OK:

update logfile set rfc931 = substring(rfc931,9,64) where rfc931 is not null;

But now I want to only modify fields that only have the DOMAIN+username value, not the already stripped down field of just username. I've tried several if then statements but none seem to come out properly. My last try was:

if substring(rfc931,8,1) = '+' then update logfile set rfc931 = substring(rfc931,9,64) where rfc931 is not null;

This gives errors also. I can't find a good resource on the internet to give me examples of syntax.

Substring
I had wanted a statement that shows the first 200 words of a long text and came up with SUBSTRING (which is not exactly what i wanted but still works)
my question though is how can i fix up this select statement

SELECT SUBSTRING(bigdescription FROM 1 FOR 200) package, name, streetnumber, address, addressinfo, bigdescription, type, events, food, music, location, days, icons FROM listing.i want to substring only the bigdescription.

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)

Order By Substring?
Is this at all possible in any way or form?

$query="SELECT title,id,date FROM `content` WHERE `user`='$user' ORDER BY SUBSTRING(title,(INSTR(title,';;')+2)) ASC ";

Retrieve Substring
i have created a website running on MySQL and have a description field in one of that tables. I would like to display, say the first 100 words from this field with a linked more button allowing you to go and view the rest. Can anyone tell me if this is possible in SQL or do i require to bring the whole field back?

Substring Question
i have database containing the field urldetails and the sentence

life is like a box of chocolates you never know which one you get next

If i query the word chocolates i want to return the result chocolates along with the next 3 words along so it will return "chocolates you never know"

i tried to do this with the following query but doesnt work can someone help

CODEselect substring(urldetails,LOCATE(urldetails,'chocolates'),3) from tableA where urldetails LIKE '%chocolates%';

Substring Problems
I can't seem to get substrings working with my database. Is this syntax correct?

SELECT Student_First_Name, SUBSTRING(Student_First_Name,0,2) AS ABBR FROM Student;

Here's an image of what I get:

I have the same tables in an oracle database and have no problem running the above query (with the change of SUBSTRING to SUBSTR of course).

Eliminate Substring
I'm totally new to MYSQl.
I just would like to know how to remove a sustring "abcdef" which is at the end of all the records of table Ihave.
The records have all a diffrent number of character.
The substring"abcdef" is always is always the last word of the field.

Changing A Substring
i need some help with a sql query that will replace a string in a bunch of fields.
for example the select that shows the fields is which need changing is "SELECT link FROM links WHERE like LIKE '%ID123%'"
i want to change all of the 'ID123' occurances to a different string.
the urls are all different but the 'ID123' part is the same in all of them. so keep the url but change the ID123
but how can i do this?

Substring Count
I like to count the number of Substrings in a datafield.
It's important to noch use any external scriptionlanguage.
Example
Table
fields[id|string|counter] with the Folowing content [2|my = test = string|0]
I need an SQL-Stemant, that counts the number of '=' in string and stores that number
as INT in field counter;
Something like
UPDATE table SET counter=COUNT_MY_SUBSTRINGS( string );

Substring In A Field
I have this table and I want to display a list with the names of the singer just once.Code:
Elvis
Beales
Ray

The name of the singer is part of a string in the field DESCRIPTION. If I use:
SELECT DISTINCT DESCRIPTION FROM songs, it doesn't work as I want.
Table songs:
Code:

ID_SONGDESCRIPTION
1Elvis/Don't
2Beatles/Help
3Beatles/Something
4Elvis/Suspicious Mind
5Beatles/Misery
6Ray/Hit the Road

Group By A Substring
I am trying to create pre-sorted zip code bundles using a mysql table.
I need to group zips by 5 digits, then by the first 3 digits of the zip
for the 5 dig I use

Select zip,count(*) from 'ziptable' group by zip

but now I need to repeat this grouping only by the first 3 digits of it

Substring From A Field
i have loads of records in table A and i want to place all distinct substring_index(list of subjects,'stringToLookFor',1) in table B. The problem is how to do it when "list of subjects" is a dynamic parameter. I guess i will need some extra selects etc, but i have no idea how to solve it atm.

insert into tableB (fileSubject)
select distinct substring_index(whatOnEarthDoIPutHere????,'stringToLookForThatIsInAllStrings',1);

How To Find Rows Where SUBSTRING Is...
I want to extraxt all rows from a table where some column starts with some given letter. For exampple : Give me all brands from the table brands where the first letter of the brand is an 'a' ?

About Substring(string, Number)
myTable1

n city
1 Tokyo
2 New York
5 Peking
6 Nagoya
7 Paris
41 Chicago

myTable4

param
c=4
c=2
n=41&k=5
c=1
n=5
c=2
c=2
n=1&cate=6
c=2
n=7
c=1
I have data in myTables like the above.
The following code produces the following result.

code

select city
from myTable1

join myTable4 on substring(param,3)=myTable1.n
where left(param,1)='n'
order by n

result

Tokyo
Peking
Paris
Chicago
I like to know why Tokyo and Chicago are retrieved.

In my think,

Peking which has "5" in the column n is retrieved because there is n=5 in myTable4.
Paris which has "7" in the column n is retrieved because there is n=7 in myTable4.

But Tokyo which has "1" in the column n is retrieved although the substringparam,3) value "1&cate=6" is different from 1.

And Chicago which has "41" in the column n is retrieved although the substringparam,3) value "41&k=5" is different from 41.

Select Substring In Search
I have a table with a text-field that i do fulltext searches on. If I get a hit when I search I would like to retrive a short excerpt from the text where my searchwords where found. Can this be done in the same query or do I have to retive the whole text and fix it with php?

Seeking Whole Words, Not A Substring
I am using mysql 4.1.1 and I am currently encountering some problems with the string-comparison functions.

I would like to have a search, that only returns a result, if the searchvalue is identical to a whole word in the database.
I do not want to have any substring-results.

For instance, I am searching for "dog". At present, I will receive results containing "dog" as substring only.
So there will be results as "hotdog", "endogenic" or "boondoggle", but, of course, also the string "dog", because "dog" is a substring of "dog", too.

I tried to work with some regexp-queries, but it didn't work at all.
Do you have any idea, how I can perform a whole-word-search without using the fulltext-search?

Deleting A Substring With A Wildcard?
I have a column of varchar that I would like to modify by deleting any text enclosed by parentheses. For example, if the string is "I am (not) confused" I want to return "I am confused".

I have been trying the following command:
update assignees set cleaname=replace(cleaname,' (%) '," ");

Unfortunately, this is returning zero rows changed even though I know there are plenty of instances of paren text. Do wildcards not work in this context? Is there some other way to do what I want?

IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere!

The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over Â£300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....

Select Left / Substring In Mysql
I have a date of birth field in a user table in the format yyyymmdd

I want to group users by their year of birth. I haven't used select left or substring in mysql before and im a bit stumped how to use it....

Determining If A Substring Exists In Field
How can I determine if a substring exists in some comma delimited data using sql?

I need to return records where a word is found in a list of words from a field. Rather than returning every record and then doing split() and searching myself I would like to know if I can do it using sql.

SELECT Rows Having Specific SUBSTRING
Table with field "SPEC_STRING" (VARCHAR(15)). How to SELECT rows having substring '111222' at position 9 (for example: strings like 'abcdefghi111222' or '000000000111222')?

SELECT Based On Substring Or Position
I have cruddy data in a field...

record_id:cruddy_data
1:1211
2:1215
3:1289
4:1311
5:1315
6:1389
7:13110

These character strings are actually 2 sets of 2- or 3-character strings stuck in the same column due to bad DB design. I cannot break the strings into 2 columns as the data is used by another system.

Extraction Of A Substring In A List With ; Separated Values.
In my database, a have a table with a field which you can put many values with a ; between each. The software is done like this.

Exemple: Field: Version
Data: 3.0.1;1;2;4;5.1.2 etc.

I want to extract all entries in the database with version = 1.

The problem is that the result give me entries with version 3.0.1 , entries with version 5.1.2 and entries with version 1.

As you probably guess, I just want entries with version = 1.

I tried with WHERE (version IN ('1;')) but it returns nothing except entries
which version is only 1; I need something to find a substring = 1; with nothing in front
of the 1 or substring = ;1;....... something like that... or better way.

Converting A Substring In Hexadecimal Notation To Integer
I would like to retrieve the first two bytes of a string in a table and
convert the result to an integer - how can I best achieve this ? The
CONVERT() function doesn't seem to allow me to specify a base system for
the conversion.

SELECT substring(name_md5,1,2) FROM my_table;

This retrieves the substring.

SELECT CONVERT( substring(name_md5,1,2), UNSIGNED) FROM my_table;

This attempts conversion, but base10.

SELECT UNHEX( substring( name_md5, 1,2 ) ) FROM my_table;

This converts the hexadecimal string to a 'regular' string, but that is not
quite what I need either.

What would be the proper way of getting the unsigned integer value represented
by the first two bytes of a string in hexadecimal notation ?

Searching Rows Containig A Substring Fast
I am creating a server indexing files in my local area network, in
order to provide a searching feature.

So i want to make it possible to searchsuch rows where the 'name'
(VARCHAR) column contains a substring submitted by user.

an i use a full-text index to make it faster? from my first sight i
noticed that this index can be used to search for words in a string. So

the example below would not go:

searching 'cool' in 'uncoolness'

Am i right? Are there any other solutions? Maby some other databases
provide this use case?

Now() Function
I am using now function to get current date when info is added to my table. I am using a hosting provider that is 6 hrs ahead in time zone so i always have to subtract 6hrs to get the correct time. Is there anyway I can format the time to be inserted to my time zone?

Self Run Function/..
I need to delete some records from my table every 5 minutes. I thought that would be better if done via a trigger, function, etc. But I don't know how to fire a trigger every 5minutes or is there any other way to do that?

Using The AVG Function
I am trying to use this query:

SELECT average(price) FROM `data` WHERE id IN ( '3', '3', '6' )

to average the price of items users select. The problem is that when a user selects a value more than once it only includes it in the average once. How can I make it average all the values?

Looking For A Particular Function
I have a Column which consists of only DATE's and an ID code. I want to enumerate all the DATE's of a certain ID to get a grand total of a ID. Does such a function exist?

Avg Function
Why doesn't this work:

SELECT avg(value) as avg_value from playerstats where avg_value = '100' and stattype = 'r' group by avg_value

I'm trying to select the avg_value of 'r' where the avg_value = '100'.

Last Function
How do you get the last row in a grouped query. I have a table with running balances. At the end of the day, I want to find out the last balance.

select *
from accounts
group by date(dateCol)

This always gives me the first row of the grouped column dateCol.

NOW() Function
We are developing a tool here that uses a php script to insert data into a MySQL 5.0.41 database running with the INNODB engine. While tracking a bug we noticed a strange effect.

Each insert query contains a call to the NOW() function to store the create data of the record into a field automatically. Therefore we can expect in my opinion that the timestamps of the records are increasing simultaniously with the autoincrement key of the table. This actually is not the case. On the contrary the timestamps are jumping up and down with large differences. For example we have the record with ID1 that got the timestamp 14 minutes 2 seconds where ID2 got 15 minutes 15 seconds and ID3 14 minutes 9 seconds. How could this be? Does it mean that the server does not execute the statements in the order he receives them? Is it possible that it took more than a minute to execute a simple insert statement?

Max Function
I have a table with name, priority and parent as three columns...I want to get the max(priority) grouped by the parent... I am trying to get name, priority, parent, max(parent) as the four columns in the result...Could some one help me with this

select name, priority, parent from table1
select max(priority) from table1 group by parent

are the two select statements that I wish to combine...is it possible..?

Function In C
I was trying to figure out how would you write a function in MySQL using C. I saw that theres a CREATE FUNCTION syntax but am confused on how to compile and write a C function.
For example if I wanted to write a function that added an extra period after every period and called it ADD_PERIOD() how would I do that.

Example:

x = "joe runs far."
SQLSTRING = "SELECT * from Story where Sentence = '"+ADD_PERIOD(x)+"'";

this would return. joe runs far

SQL IN() Function
so i have a query such as
SELECT first_name FROM users WHERE user_ID IN (5,4,8,19,8,4) and i want the first_name to be retrieved for EACH of the cases (eg. name is repeated twice for 4 and 8)

2 Queries In 1 Function?
Is it possible to query the database twice in the same function?

If so is it possible to use results form the first query IN the second?

Count () Function
Cant anyone tell me why this very basic query won't process.
This is to give a breakdown of Mem.heard 1-12 numbers ("where did you hear about us survey") for ea of the MOrig two user types

EX:
MOrig | heardof | count_heardof

UserA 1 5 (times)
UserA 2 6 (times)
UserB 1 3 (times)
UserB 2 8 (times)

SELECT MOrig, heardof, count ( heardof ) as count_heardof
FROM Mem
GROUP BY MOrig, heardof
ORDER BY MOrig

Search-function
Does anyone happen to know how to select info from the database based on multiple strings like so:

SELECT title FROM food WHERE title LIKE '%New%' AND WHERE title LIKE '%York%' AND WHERE title LIKE '%restaurant%'

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?

Hidden_field Function
Does mysql 4.0.24 standard have a hidden_field function? I need to maintain state from one form to another and hidden_field is used in a script I have using mysql 3.22. If 4.0.24 doesn't have hidden_field function, what can I use with mysql 4.0.24, which is used on my server?

Date Function
I have a time stamp field in my orders table and I want to form a select statement where it returns all orders with an order time of two weeks ago from the current time or later so I can keep better track of unpaid orders.

Here are the fields for the orders table
orderID
order_time
order_status
etc...

SELECT orderID FROM orders WHERE ???? AND order_status=2 ORDER BY order_time ASC

Thanks for any help. The date functions are a little confusing to me from the manual.

Function ROW_INDEX
I couldn't find a function that numbers the resultset of a query. First row should be 1, second row 2 etc.

I searched for rownum, rowcount, rowindex etc with and without underscore's.

Password() Function
I've created a simple table called users. This table contains four columns. First_Name, Last_Name, User_Name, Password. I've used the password() function when inserting a user. I have read the documentation on using other types of encryption, but it's killing me that I can't get this to work. Robert has a User_Name of "Bob" and a password of password('tomato').

===============================
HOW I ADDED BOB

insert into users values("Robert","Evans","bob",password('tomato'));

1 row added yada yada

HOW I QUERY BOB

select * from users where User_Name="bob" and password=password('tomato');

Empty Set

===============================

That exact same query is in a tutorial for a simple PHP login form, but an empty set is returned even in the command prompt using MySQL by itself, therefore the script cannot move on.

Mysql_query Function
I making a loop of the returned result set from mysql_query, it is very important to know if an update occur during the loop to the table affect the result set of the loop or not ?

I need to loop blindly without see any updates or inserts occur during the loop....

Counting Function
I have a table with a list of names and some are duplicate and I would like to count them?? It's like this:

Drew
Simon
Alli
Tim
Beth
Alli
Simon
Drew
Simon
Simon
Alli

Then to export:
Drew - 2
Simon - 4
Alli - 3
Tim - 1
Beth - 1

How could I go about asking this query?

SELECT COUNT(name) FROM table

??

Random Function?
I would like to know if there is an existing random function I can use to generate a random number so I can concatenate it to a string?

AES_ENCRYPT Function
Essentially I am playing around with the aes_encrypt fucntion to securly store some data in the db. The problem I am having is that I get the following error : Data truncated for column 'foo' at row 1 and the table creating was done with:

CREATE TABLE `foobar` (
`foo` varchar(10000) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

with a varchar(10000) and doing and AES_ENCRYPT('foo','bar') one would think that it would work. But I suspect that I am doing something incorrectly.