Hi

I have a large table with allot of data in it, Im trying to find the correct SQL statement to select all the records that do not start with a letter or a number.

So some records may start with a '~' and a few '$' and even some '?'. So I would like a statement that would select all of these while leaving out any records that start with anything else, such as numbers or letters.

And on a similar theme, Im also trying to select all records that start with a number.

Ive been using the "select blah from blah where LIKE 'a%'" for each letter and ive tried to have a play and search around that statement, but still not found an answer.

Would appreciate any help on the matter :)

I don't think if there is a single query to fetch records this way, but you'll need to write a script to loop through all records and verify each one with your rules.

#select records not starting with number or letter
select fields from table where field REGEXP '^[^0-9a-z]'

#select records starting with number
select fields from table where field REGEXP '^[0-9]'

Just to add to Sasha's post:

Please visit this link explaining the use of REGEX in MySql Select Statements:
http://www.tech-recipes.com/mysql_tips484.html

Edit:
If you are using SQL Server 2005 you are better off with a CLR function, such as this:
using System;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class RegExBase
{
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static bool RegExMatch(string pattern, string matchString)
{
Regex r1 = new Regex(pattern.TrimEnd(null));
return Regex.Match(matchString.TrimEnd(null)).Success;
}
};
As described in the following article http://blogs.msdn.com/sqlclr/archive/2005/06/29/regex.aspx