SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL IS testing


SQL IS testing

Author
Message
sqldriver
sqldriver
SSC Eights!
SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)

Group: General Forum Members
Points: 974 Visits: 2518
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26125 Visits: 17539
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26125 Visits: 17539
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39374 Visits: 38549
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
sqldriver
sqldriver
SSC Eights!
SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)

Group: General Forum Members
Points: 974 Visits: 2518
Really?! There's not an ISALPHA or anything to test for woogy symbols? That's... Unsure
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26125 Visits: 17539
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26125 Visits: 17539
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
sqldriver
sqldriver
SSC Eights!
SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)

Group: General Forum Members
Points: 974 Visits: 2518
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
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26125 Visits: 17539
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
sqldriver
sqldriver
SSC Eights!
SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)

Group: General Forum Members
Points: 974 Visits: 2518
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search