SQL IS testing

Question

SQL IS testing

sqldriver

SSChampion

Points: 10440
More actions
April 15, 2013 at 11:53 am

#292526

Hi,
Does anyone have a list of all the IS (date/numeric) type syntax available? I've been trying to find a resource that just lists them all out, but I guess I'm not phrasing my search correctly.
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

erikd (4/15/2013)
Hi,
Does anyone have a list of all the IS (date/numeric) type syntax available? I've been trying to find a resource that just lists them all out, but I guess I'm not phrasing my search correctly.
Thanks

I think you did list them all out, both of them. ISDATE and ISNUMERIC are the only two I can think of. They are really only somewhat useful, well actually ISNUMERIC is pretty useless. It should be named ISValueSomethingThatCanBeConvertedToAnyDataTypeThatHasNumbersInIt.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Oh yeah ISNULL too.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Sean Lange (4/15/2013)
erikd (4/15/2013)
Hi,
Does anyone have a list of all the IS (date/numeric) type syntax available? I've been trying to find a resource that just lists them all out, but I guess I'm not phrasing my search correctly.
Thanks
I think you did list them all out, both of them. ISDATE and ISNUMERIC are the only two I can think of. They are really only somewhat useful, well actually ISNUMERIC is pretty useless. It should be named ISValueSomethingThatCanBeConvertedToAnyDataTypeThatHasNumbersInIt.

Or to shorten the name ISValueSomethingThatCanBeConvertedToAnIndeterminateNumericType. Actually, I don't that that is any shorter.

sqldriver SSChampion Points: 10440 More actions · Answer 4

Really?! There's not an ISALPHA or anything to test for woogy symbols? That's... :unsure:

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

erikd (4/15/2013)
Really?! There's not an ISALPHA or anything to test for woogy symbols? That's... :unsure:

ISAPLHA?? What would you be testing for? Not exactly sure what "woogy symbols" are. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Lynn Pettis (4/15/2013)
Sean Lange (4/15/2013)
erikd (4/15/2013)
Hi,
Does anyone have a list of all the IS (date/numeric) type syntax available? I've been trying to find a resource that just lists them all out, but I guess I'm not phrasing my search correctly.
Thanks
I think you did list them all out, both of them. ISDATE and ISNUMERIC are the only two I can think of. They are really only somewhat useful, well actually ISNUMERIC is pretty useless. It should be named ISValueSomethingThatCanBeConvertedToAnyDataTypeThatHasNumbersInIt.
Or to shorten the name ISValueSomethingThatCanBeConvertedToAnIndeterminateNumericType. Actually, I don't that that is any shorter.

Maybe ISValueNumberish?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sqldriver SSChampion Points: 10440 More actions · Answer 7

Sean Lange (4/15/2013)
erikd (4/15/2013)
Really?! There's not an ISALPHA or anything to test for woogy symbols? That's... :unsure:
ISAPLHA?? What would you be testing for? Not exactly sure what "woogy symbols" are. 😉

Er... things that aren't letters or numbers.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

erikd (4/15/2013)
Sean Lange (4/15/2013)
erikd (4/15/2013)
Really?! There's not an ISALPHA or anything to test for woogy symbols? That's... :unsure:
ISAPLHA?? What would you be testing for? Not exactly sure what "woogy symbols" are. 😉
Er... things that aren't letters or numbers.

That would be dependent on a number of things, mostly what language? What is not considered a letter by you may very well be a legitimate letter somebody else that speaks another language. If you mean a-z and 0-9 that is what regex is for.

The rules for setting up a built in function would be far more complicated than a quick test of your own.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sqldriver SSChampion Points: 10440 More actions · Answer 9

Sean Lange (4/15/2013)
erikd (4/15/2013)
Sean Lange (4/15/2013)
erikd (4/15/2013)
Really?! There's not an ISALPHA or anything to test for woogy symbols? That's... :unsure:
ISAPLHA?? What would you be testing for? Not exactly sure what "woogy symbols" are. 😉
Er... things that aren't letters or numbers.
That would be dependent on a number of things, mostly what language? What is not considered a letter by you may very well be a legitimate letter somebody else that speaks another language. If you mean a-z and 0-9 that is what regex is for.
The rules for setting up a built in function would be far more complicated than a quick test of your own.

English, 95% of the time. I'm not talking about Unicode foreign language characters (finally). I'm mostly talking about ~!@#$%^&*()-_=+[{]}\|;:'",<.>/? and sometimes `. You know, those guys.

I (sort of) get regex, and have a function that I believe I found on here for removing non-alphanumeric characters from strings (let me know if my terminology is wrong; the script was called parsealhpa). But it doesn't do anything other than clean them out. Like, I couldn't use it to just write a query that would find a value containing something outside of the patindex, or include/exclude values based on if they have not-letter-or-number-squiggly-lines in them

I guess I was wondering if there was a more smarterer way of doing things

CREATE FUNCTION [dbo].[ParseAlpha]

(

@string VARCHAR(8000)

)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @IncorrectCharLoc SMALLINT

SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)

WHILE @IncorrectCharLoc > 0

BEGIN

SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')

SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)

END

SET @string = @string

RETURN @string

END

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Well you could create your own IsAlpha. I would extend the version you found to at least include spaces and periods.

something like this might help you along.

declare @ScalarVal varchar(100) = '123.'

select case when @ScalarVal not like '%[^a-z0-9 .]%' then 1 else 0 end as IsAlpha

You could turn that into a function if you wanted.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sqldriver SSChampion Points: 10440 More actions · Answer 11

Sean Lange (4/15/2013)
Well you could create your own IsAlpha. I would extend the version you found to at least include spaces and periods.
something like this might help you along.
declare @ScalarVal varchar(100) = '123.'
select case when @ScalarVal not like '%[^a-z0-9 .]%' then 1 else 0 end as IsAlpha
You could turn that into a function if you wanted.

It does. Thanks.