Hello,

SELECT CASE replace(@s,'sec','') WHEN 12345 then 'y' ELSE 'n' end

returns 'y'

I want to test if the numeric portion of the string is < 15000.  If I put
15000 in place of 12345 then the statement returns 'n' - only tests for =. 
How can I test for < or >  and still return a 'y' if the number portion of
'sec12345' is less than 15000?

Thanks,
Rich

Sorry, here is the corrected statement:

SELECT CASE replace('sec12345','sec','') WHEN 12345 then 'y' ELSE 'n' end

returns 'y'

I want to test if the numeric portion of the string is < 15000.  If I put
15000 in place of 12345 then the statement returns 'n' - only tests for =. 
How can I test for < or >  and still return a 'y' if the number portion of
'sec12345' is less than 15000?

Try the expression like:

SELECT CASE WHEN CAST( REPLACE( 'sec12345', 'sec',
                            SPACE(0) ) AS INT ) < 15000
            THEN 'y' ELSE 'n'
       END ;

Is this some kind of a data cleanup routine? If your business relies on
portions of the data values, you'd be better of representing them as
individual values in separate columns.

Thank you for your reply. 

What I need to do is this:  I have an email client app (that I wrote in
VB.Net) which reads data from our sql server tables.   We run an online/phone
subscription operation.  If people subscriber to our products from online,
they automatically get an email containing a userID and password.  THe userID
from online will contain 1 to 3 alpha chars and 4 numeric chars.  If they
subscribe by phone or regular mail and are manually entered into our system
they get a userID/password where the userID may or may not contain any alpha
chars but will contain 5 numeric chars.  The numbers are based on the value
of the Identity column that first generates a number. 

It is an old system that has been getting modernized.  The web tables have
lower Identity numbers than the original data tables (the web tables are
newer) but everything ends up in a table that my email app reads from.  My
email app gets invoked when people sign up by phone or mail.  My app has to
distinguish between the web subscribers and non-web subscribers.   It is a
somewhat hokey system, and we have a big annual conference coming up in a
month, so don't want to tweak anything until after the conference.  After
that we will upgrade from Sql Server 2000 to Sql Server 2005 and will
normalize tables that are currently not normalized (that is part of the
problem - a big part).  I took over for some guys at the beginning of the
year.  They told me the issues and wished me good luck.  That was nice of
them :).   When us new people start the upgrade, we will correct the issues. 
In the meantime, I have to do a lot of patch work.

Right now the web numbers are around 9500.  So I have been testing for

len(replace('sec1234', 'sec', '')) < 5 then don't read - if
len(replace('sec12345', 'sec', '')) = 5 then read.   

The main data tables are aound 45000 - which is len = 5.  When the web
tables reach 10000 - not long from now - testing len won't work.  So I was
going to test if the numeric portion of the userID was less than 15000.  By
15000 we should have the new system in place and be using all normalized
tables.

Rich

Show quoteHide quote

Get last date in result set
Managed Stored Procedures
Query Problem
Update/Insert and Delete Query Optimisations
Check constraint script problem
Rounding datetime
Deallocate cursor overhead
Mdf and Ldf files
Trigger testing
Can I create an index on a varable table