SELECT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT

Author

Topic

petem
Starting Member

44 Posts

Posted - 2004-12-14 : 11:27:56

Hey all,

Ive got to return some data from a table for an asp page. The problem is that the data is in a total mess.

example...

the column data shoud be :
SL-TOP 340

However, this is actually in the table as:
SL TOP 340
SLTOP 340
SL340

What I would like to do is check the type of character after the 'SL'

if it's a numerical character (ie: SL340) then show it. if its a letter (ie: SLTOP 340) don't show it.

I have managed to used the following on a simple string:


DECLARE @str1 VARCHAR(200)
DECLARE @str2 VARCHAR(10)
DECLARE @str3 VARCHAR(200)
DECLARE @Temp VARCHAR(1)

SET @str1 = 'SL TOP 340'
SET @str2 = 'SL TOp'
SET @str3 = REPLACE(@str1, @str2, '')
IF ASCII(@str3) IN (32, 45, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57) 
BEGIN
	SET @temp = 'y-'
END
ELSE
BEGIN
	SET @temp = 'n-'
END
SELECT @str3, @Temp

(The ASCII codes are space, hyphen and numbers 0-9)
hope that's explained well enough!

This works for a single string in this example - But how would I crowbar this into a searchResults stored procedure...I've been told to use a temporary table but im stuck as to how to check the value of each row..

Thanks for reading,

Pete

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-14 : 12:10:13

See
http://www.nigelrivett.net/RemoveNonNumericCharacters.html

You can use it on a substring of the field.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

petem
Starting Member

44 Posts

Posted - 2004-12-15 : 04:07:25

apologies nr - I dont understand the relevance there. You may need to elaborate for me - Not too strong on this kinda thing

I think I need to:

1. Get all the records returned from the results
2. Check that the returned string for each record is not a certain ASCII code.
3. Return only the records that have certain ASCII codes.

Cheers,

Pete

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources