Nik's Technology Blog

Travels through programming, networks, and computers

MySQL Replace Function

I never knew this function existed. Well I never!

MySQL Replace(), here's the syntax.

update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');

I've also successfully used nested Replace() functions, as above, within a select statement to reform field values for output.

What would we do without it!

Some more MySQL snippets

MySQL vs TSQL Concatenation CONCAT()

Following on from an entry posted last year concerning converting SQL queries from Microsoft TSQL to MySQL I've come across another function which is treated somewhat differently in MySQL than TSQL.

The following SQL code adds the .jpeg file extension onto the end of the image names from tblImages.

TSQL Concatenation

SELECT ImageName+'.jpeg' FROM tblImages

The above SQL syntax is used on Microsoft SQL Server, Microsoft Access and Sybase

MySQL Concatenation

SELECT CONCAT(ImageName,'.jpeg') FROM tblImages

Oracle Concatenation

SELECT ImageName || '.jpeg' FROM tblImages

The Oracle syntax is the syntax recommended by the SQL specification. It uses 2 pipes || instead of a plus + sign. This avoids the problems of trying to concatenate integer fields which might otherwise result in an unwanted addition.

Previous entry: T-SQL TO MYSQL CONVERSIONS

T-SQL to MySQL Conversions

I've been converting my live web statistics page to PHP and MySQL and after spending a while trying to find MySQL alternatives to Transact SQL (T-SQL) only SQL statements, I thought I'd share a few common solutions to the problems I eventually managed to solve.

Microsoft developers commonly use TOP in a select statement to select the first n rows from a table. The MySQL equivalent is LIMIT, which is superior in that you can specify what row to start on and well as how many to retrieve.

SELECT TOP 20 * FROM MY_TABLE

SELECT * FROM MY_TABLE LIMIT 0,20

Other problems arise from T_SQL's use of Visual Basic (VB). So statements using LCASE, UCASE, INSTR and MID won't work. MySQL of course has alternatives...

SELECT LOWER(name) FROM MY_TABLE

SELECT UPPER(name) FROM MY_TABLE

The SUBSTRING MySQL statement is equivalent to MID or LEFT if you start at the first character. The following statement would result in abcd.

SELECT SUBSTRING('abcdefghijklmnopqrstuvwxyz',1,4)

VB's INSTR is another useful function in T-SQL, the MySQL equivalent is LOCATE. Like INSTR you define a starting point in which you would like to start searching within the string. The following SQL statement would result in 4.

SELECT LOCATE('d','abcdefghijklmnopqrstuvwxyz',1)

MySQL's version of T-SQL's LEN function works in exactly the same manor, only it's called LENGTH. The following statement will result in 26.

SELECT LENGTH('abcdefghijklmnopqrstuvwxyz')

MySQL Manual - String Functions

Importing data from one Database to another with slightly different sized fields?

Preparation is the key to a faultless import.

Sometimes it's useful to know the maximum length of the data you store in your database. Even if you know the maximum size of the cell, the actual data stored may be smaller.

In Microsoft SQL use:

SELECT MAX(LEN(firstname))

FROM tblData

This MS SQL command will find the maximum length of the data in the firstname field of tblData table.

Using this method you can avoid having to increase the destination tables field sizes in cases where you haven't actually used the full capacity in the source database.

So if the source field size for firstname is varchar 50 and the destination is varchar 40, you can use the above SQL to determine if anyone's first name is actually longer than 40 characters. If it's not then you don't need to increase the cell size in the destination table.

If you get the following error when you try to use the LEN() SQL statement above, then you are trying to find the maximum length of a text field.

Error: Argument data type text is invalid for argument 1 of len function.

For text fields in SQL use Datalength() instead of LEN().

SELECT MAX(DATALENGTH(Description))

FROM tblJobs

The Datalength() function which will return the length of any expression, in this case the maximum length of the description field from the tblJobs table.

This can be used on all data types including text, ntext, image and varbinary.

Of course a perfectly designed database would have as little spare capacity in fields as possible, but with fields such as people's names this is hard to achieve this.