SQL Server INSTR Equivalent

Access Database queries support the use of INSTR to search inside a datafield for a series of characters - INSTR(DataField, String):

INSTR(DataField, 'y') 

In the above example, if the value in DataField was 'yellow', the returned value would be a 1, as 'y' is the first character of the datafield. Using the same value (yellow), INSTR(DataField, 'o') would return a 5.

INSTR, however, doesn't work in SQL Server. SQL Server does, however, have an equivalent - the CHARINDEX - the only difference being that that order of the parameters is reversed - CHARINDEX(String, DataField):

CHARINDEX('y', DataField)

Returning the Length of an Text or nText Field

In SQL Server, if you try len(FieldName) to return the number of characters in a field and the filed is a type of Text or nText, you get an error:

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

The answer to this is the Datalength() function which will return the length of any expression. This can be used on all data types including text, ntext, image and varbinary.

It returns the actual number of bytes in the field.

Retrieving a Random Record from a Database

I was working on one of my new sites over the weekend and needed to retrieve a random record from one of the tables in my database. A few minutes of searching revealed several solutions.

Here's the SQL Server 2000 solution I ended up going with, although it might not work if you aren't using an integer as the record ID:

    SELECT TOP 1 *  
    FROM YourTable 
    ORDER BY NEWID() 

I found one to use if you're using IDENTITY as a unique idetifier for the record id, but I didn't test it since I'm using n auto-incrementing integer:

    SELECT TOP 1 *
    FROM YourTable 
    ORDER BY RAND((1000*IDColumn)*DATEPART(millisecond, GETDATE()))  

If you're using Access, here's one (although it's not nearly so elegant). This one requires some VBScript to generate the random number seed:

<% 
    Randomize() 
    randNum = (CInt(1000 * Rnd) + 1) * -1 
 
    set conn = CreateObject("ADODB.Connection") 
 
    sql = "SELECT TOP 1 cols," & _ 
        "r = Rnd(" & randNum & ")" & _ 
        "FROM TableName " & _ 
        "ORDER BY r" 
 
    set rs =     conn.execute(sql) 
 
    response.write rs(0) 
 
    ' ... 
    rs.close: set rs = nothing 
    conn.close: set conn = nothing 
%>