MYSQL :: Eliminate Substring

Eliminate Duplicate
i whould like to know is it's possible to write a sql command to eliminate possible duplicate entries in a table...
for example a table with 3 columns
i want to eliminate duplicates of column 2.

Eliminate LIMIT From Sub Query
I have a single table that tracks all revisions to all issues. The format is like so:

id - auto_increment
issue_id - foreign key to an issue
changed_at - timestamp
assignee - foreign key to a user
change_made - varchar of change that was made at that time.

I need to select the latest change to every issue for one specific user. I would normally do this with:

SELECT * FROM historical_data AS hd WHERE hd.id IN (SELECT id FROM historical_data WHERE hd.id = id ORDER BY changed_at DESC LIMIT 1) AND assignee = 6;

Of course this gives the "You can use LIMIT & IN at the same time" error.

Is there something else I can do to get the same result ... besides moving to Postgres?

Eliminate ALL Duplicate Records
I am trying to compare 1 field table 1.Mat_Num and compare it to table 2.Mat_Num
and eliminate all overlap so that only fields in table 1.Mat_Num that are not in table 2.Mat_Num are remaining. Or something like the following query:

Select table1.Mat_Num
From table1.Mat_Num, table2.Mat_Num
Where table1.Mat_Num <> table2.Mat_Num

I have used LIKE, NOT LIKE, IN, NOT IN, JOIN, and everthing else found on google just short of scripting. Obvously, my current results have been a loop listing every instance where table1.Mat_Num(Field X) NOT table2.Mat_Num(Field X). so it is not taking table1.Mat_Num(Field 1) and then searching all fields in table2.Mat_Num for an occurance.

Eliminate Rows In A Query (without Many OR)
This is my query :

SELECT cas.id,cas.noCas,cas.nomFictif,cas.prenom,cas.naissance FROM cas WHERE LEFT(noCas, 3)<'300' AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598 ORDER BY noCas

There must be a more efficeint way to find what i want than this :

AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598

Something like cas.id is not (list of values)...

Eliminate Duplicate IDs From A Table
I've got a 'books' table with 'id', 'name', 'author' and
'isbn' fields.

Due to an error, my books are duplicated, so I've got (e.g.):
430 - Moby Dick - Herman Melville - 0192833855
430 - Moby Dick - Herman Melville - 0192833855
528 - A Tale of Two Cities - Charles Dickens - 0141439602
528 - A Tale of Two Cities - Charles Dickens - 0141439602

And when trying to find a book by its name, I get two
entries instead of one.

I would like:
1) To set id as "unique id"
2) To temove the duplicates IDs from the table.

Eliminate 'almost Duplicate' Rows
I have a query that works fine but produces a resultset with duplicates. The results are unique in that they have different ID fields, but all the other fields can be identical. I would use DISTINCT to eliminate normal duplicates but I havent got a clue how to go about eliminating rows when all but the ID fields are duplicated.

Eliminate Dropdown - Set Default Parameter
I want to eliminate a dropdown that allows the user to set the number of days they want their information posted. The maximum allowed is set in the admin panel and the user can select up to that number. I want to be able to retain all rescords unless they are deleted by the Admin. or account user. This is the current dropdown code:

<select name="auction_period">
<?
for($i=1; $i<=$rate["max_period"];$i++)
{
?>
<option value="<? echo $i; ?>"><? echo $i; ?></option>
<?
}
?>
</select>

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 ";

Substring Function
I am trying to figure out a SQL statement. I have created a table called employees with fname, lname, and email fields. I have several records in this table. All records have values in the email field, but not the fname or lname fields.

I would like to create a sql statement to parse the email field (which consists of the first and last names of the employee in this format: first.last@company.com).

I know I am way off, but this is what I have so far:
UPDATE `employees` SET `fname`='what goes here?'
UPDATE `employees` SET `lname`='what goes here?'

Being new to SQL, what function do I use to search for a particular substring of the email field, such as 'first' for the fname, and 'last' for the lname? I am aware of the left() function and mid() function, but the strings vary in length.

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).

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?

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?

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.

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.