Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL IS testing Expand / Collapse
Author
Message
Posted Monday, April 15, 2013 11:53 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:04 AM
Points: 488, Visits: 1,993
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

Post #1442431
Posted Monday, April 15, 2013 12:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1442453
Posted Monday, April 15, 2013 12:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1442454
Posted Monday, April 15, 2013 12:51 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 20,804, Visits: 32,735
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.



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)
Post #1442456
Posted Monday, April 15, 2013 12:51 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:04 AM
Points: 488, Visits: 1,993
Really?! There's not an ISALPHA or anything to test for woogy symbols? That's...
Post #1442457
Posted Monday, April 15, 2013 12:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
erikd (4/15/2013)
Really?! There's not an ISALPHA or anything to test for woogy symbols? That's...


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 Moden's 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)
Post #1442462
Posted Monday, April 15, 2013 12:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1442463
Posted Monday, April 15, 2013 1:16 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:04 AM
Points: 488, Visits: 1,993
Sean Lange (4/15/2013)
erikd (4/15/2013)
Really?! There's not an ISALPHA or anything to test for woogy symbols? That's...


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



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


Post #1442468
Posted Monday, April 15, 2013 1:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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...


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 Moden's 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)
Post #1442475
Posted Monday, April 15, 2013 1:42 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:04 AM
Points: 488, Visits: 1,993
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...


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

Post #1442481
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse