Using Substring with a Where Clause

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

General SQL Server Forums

New to SQL Server Programming

Using Substring with a Where Clause

Author

Topic

xxMarkDxx
Starting Member

3 Posts

Posted - 2012-04-13 : 04:36:30

Hi i hope someone can help i am new to using T-SQL so i will apoligise if this is a basic question

I have the following piece of code that i run to update a field in a table

Use BchTelephony
Update dbo.BchInboundData
Set BchCode = Substring (Name,5,3)

what i am trying to do is only ren the update if the substring returns a number , is there a way off doing this without having to add a where clause that will have to say Not 'A' Not 'B' tc etc

Any help witll be gratefully aprreciated

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-13 : 05:43:37

You can add a where clause. If it is all positive integers (i.e., you want to look for only digits 0 through 9), then you can do this:

WHERE
   SUBSTRING(Name,5,3) NOT LIKE '%[^0-9]%'

Or if you have negative numbers also use '%[^0-9-]%', decimal points, then'%[^0-9-.]%' etc.

Another way is to use the isnumeric function.

WHERE
   ISNUMERIC(SUBSTRING(Name,5,3)) = 1

xxMarkDxx
Starting Member

3 Posts

Posted - 2012-04-13 : 06:37:24

Thanks sunitabeck that is great

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 11:37:09

quote:
Originally posted by sunitabeck

You can add a where clause. If it is all positive integers (i.e., you want to look for only digits 0 through 9), then you can do this:
WHERE
SUBSTRING(Name,5,3) NOT LIKE '%[^0-9]%'
Or if you have negative numbers also use '%[^0-9-]%', decimal points, then'%[^0-9-.]%' etc.

Another way is to use the isnumeric function.
WHERE
ISNUMERIC(SUBSTRING(Name,5,3)) = 1

ISNUMERIC is not fully reliable

see

http://beyondrelational.com/modules/2/blogs/70/posts/10803/enhanced-isnumeric-function.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-13 : 11:48:52

quote:
ISNUMERIC is not fully reliable

I would prefer to call it GENEROUS rather than unreliable. Everything that returns a 1 for ISNUMERIC (such as $,-,1e0,2d2,space character etc.) can be converted to at least one numeric type. So SQL Server is being generous/liberal in considering what is numeric.

SELECT CAST('$' AS MONEY);
SELECT CAST('-' AS INT);
SELECT CAST('1d0' AS FLOAT);
SELECT CAST (' ' AS INT);
SELECT CAST('2e2' AS FLOAT);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 11:53:53

quote:
Originally posted by sunitabeck

quote:
ISNUMERIC is not fully reliable
I would prefer to call it GENEROUS rather than unreliable. Everything that returns a 1 for ISNUMERIC (such as $,-,1e0,2d2,space character etc.) can be converted to at least one numeric type. So SQL Server is being generous/liberal in considering what is numeric.
SELECT CAST('$' AS MONEY);
SELECT CAST('-' AS INT);
SELECT CAST('1d0' AS FLOAT);
SELECT CAST (' ' AS INT);
SELECT CAST('2e2' AS FLOAT);

I meant its not fully reliable when you're using it for stringent numeric checks like in this scenraio

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources