Checking a string for unusual characters

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)

Checking a string for unusual characters

Author

Topic

mcrors_calhoun
Starting Member

22 Posts

Posted - 2006-09-05 : 12:24:22

I have a function that checks an incoming string to see if it can be properly converted into a date. If the day part of the string contains a 00 I must change this to a 01 and the same applies for month. If the string comes in as '' or '0' I have to return a null. I have done this like so

BEGIN
DECLARE @temp_date char(10)
SET @temp_date = @input_date
IF SUBSTRING(@input_date, 1, 1) = '0'
SET @temp_date = null
ELSE IF SUBSTRING(@input_date, 5, 2) = '00'
SET @temp_date = SUBSTRING(@input_date, 1, 4) + '0101'
ELSE IF SUBSTRING(@input_date, 7, 2) = '00'
SET @temp_date = SUBSTRING(@input_date, 1, 6) + '01'
ELSE IF @input_date = ''
SET @temp_date = null

RETURN @temp_date
END

I must now add some functionality that will check for letters of unusual characters like *,%,# etc. I was think that I might be able to just add in another ELSE IF statement that will search the string for any character that isn't 1...9. Is this possible??? And if so how can I do this???

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 12:43:42

Add a IF PATINDEX('%[^0-9]%', @inputdate) > 0 SET @Temp_Date = NULL first.

Peter Larsson
Helsingborg, Sweden

EDIT: Forgot the wildcard characters.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-09-05 : 13:38:21

What do your input strings look like? (Both good and bad dates...)

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-05 : 14:18:34

I would filter this on the front end and simply not allow bad data to come in.

(uh huh, right, in a perfect world)

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

mcrors_calhoun
Starting Member

22 Posts

Posted - 2006-09-06 : 09:44:31

a good date would look like this
'20060906' would be today's date.

A typical bad date would be '20060000', in which case the month and day would be set to 0101
or another typical bad date would be '20060900', then only the day would be changed to 01.

The other common bad dates would be '0', '00000000', or '' in which case the function returns a null. I have these covered in the function above but what I don't have is if a date comes is as something crazy, like 'fdas' or '$&^$&^' then I want to return a null.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 10:53:44