We have a field typed as varchar(50) that usually contains only a numeric
but that can contain text.
Examples: "1890", "18 90", "L1890", "1890L"," 189O"

Problem is because this is looked upon as a numeric by users, they expect
a numeric sort. Is there anything in SQL that would pull just the numeric
data out of the field and allow me to sort it as a number?

Many thanks!

One method is to create your own user-defined function for ordering:

CREATE FUNCTION dbo.fn_IgnoreNonNumericCharacters(@Value varchar(50))
RETURNS varchar(50)
AS
BEGIN
    DECLARE
        @NumericCharacters varchar(50),
        @StartPosition int
    SET @NumericCharacters = ''
    SET @StartPosition = 1
    WHILE @StartPosition <= DATALENGTH(@Value)
    BEGIN
        IF SUBSTRING(@Value, @StartPosition, 1) LIKE '[0-9]'
        BEGIN
            SET @NumericCharacters = @NumericCharacters +
                SUBSTRING(@Value, @StartPosition, 1)
        END
        SET @StartPosition = @StartPosition + 1
    END
    RETURN RIGHT(REPLICATE('0', 50)  + @NumericCharacters, 50)
END
GO

SELECT MyData
FROM (
    SELECT '1890' AS MyData
    UNION ALL SELECT '18 90'
    UNION ALL SELECT 'L1890'
    UNION ALL SELECT '1890L'
    UNION ALL SELECT ' 189O'
    UNION ALL SELECT ' 0'
    UNION ALL SELECT '2'
    UNION ALL SELECT '100'
UNION ALL SELECT '200'
    ) AS MyTable
ORDER BY
    dbo.fn_IgnoreNonNumericCharacters(MyData)

Show quoteHide quote

VARCHAR(50)!  A magical number oftern used by newbies who have no
schema design.  What you need to do is clean up the data and add a
check constraint to control the contents of the column.  The ability to
add constiants is one many reasons that a coumn is nothng like field.
Maybe something like this?

foobar CHAR(5) NOT NULL
CHECK (foobar LIKE '0[0-9][0-9][0-9][0-9]
             OR foobar LIKE 'L[0-9][0-9][0-9][0-9]'
             OR foobar LIKE '[0-9][0-9][0-9][0-9]L'

The other trick is to nest REPLACE() functions to get rid of
non-numerics.  That has been posted many, many times.

Thank you very much guys, that function works perfectly.

Unfortunateley I am just assigned the duty of reporting on the data and have
no control how the DB was created. I have to live with the acknowledeged
carelessness of others and work around it. I agree the data should have been
cleaned up and typed properly on the way in.

Sorry for not discovering this solution if it already existed. I thought I
had done a thorough search through the threads for related topics but did
not find.

Again, thank you for your help it is much appreciated!

Show quoteHide quote

how to obtain further error information?
Why is this statement blocking...
NESTED SQL QUESTION and aggregate function
CLR Triggers and inserted table
Query Help - Counting Dates and Times