Check if string could be converted to number

I need to check if string could be converted to a int without throwing any
errors.
I need to do something like this
DECLARE @.s varchar(20)
DECLARE @.i int
--if following is possible
@.i=CAST (@.s as int)
--then
SELECT @.1
--else
SELECT 0
if string is not a number I really don't need to deal with it in the first
place.
The real life example of my scenario is checking uniqueness of check number
for bank transactions. If user writes ATM for check number we don't need to
check the uniqueness.
Could it be done?
Thanks,
Shimon.There's a built-in function in SQL - ISNUMERIC. Look it up in Books Online.
However, it has some issues. They are illustrated here:
http://www.aspfaq.com/show.asp?id=2390
..and more! ;)
ML|||Hi Shimon
you can use ISNUMERIC for this
select isnumeric(@.s)
For eg:
if Value of @.s is '123' then the value returned is 1
if Value of @.s is '123a' then the value returned is 0
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Shimon Sim" wrote:

> I need to check if string could be converted to a int without throwing any
> errors.
> I need to do something like this
> DECLARE @.s varchar(20)
> DECLARE @.i int
> --if following is possible
> @.i=CAST (@.s as int)
> --then
> SELECT @.1
> --else
> SELECT 0
> if string is not a number I really don't need to deal with it in the first
> place.
> The real life example of my scenario is checking uniqueness of check numbe
r
> for bank transactions. If user writes ATM for check number we don't need t
o
> check the uniqueness.
> Could it be done?
> Thanks,
> Shimon.
>
>|||Shimon wrote on Wed, 17 Aug 2005 08:25:30 -0400:

> I need to check if string could be converted to a int without throwing any
> errors.
> I need to do something like this
> DECLARE @.s varchar(20)
> DECLARE @.i int
> --if following is possible
> @.i=CAST (@.s as int)
> --then
> SELECT @.1
> --else
> SELECT 0
> if string is not a number I really don't need to deal with it in the first
> place.
> The real life example of my scenario is checking uniqueness of check
> number for bank transactions. If user writes ATM for check number we don't
> need to check the uniqueness.
> Could it be done?
> Thanks,
> Shimon.
Try
DECLARE @.s varchar(20)
DECLARE @.i int
/*set value of @.s here*/
SET @.s = 'test'
IF (ISNUMERIC(@.s) = 1)
SET @.i = CAST(@.s as int)
ELSE
SET @.i = 0
SELECT @.i
You'll get a response of 0. Change 'test' to '1000', you'll get 1000.
Dan|||Oh, one thing I missed in my reply - if the string is numeric, but too large
to fit into an int, you'll get an error, so you should have some check on
the string length to determine if it'll fit, or cast into the largest
numeric datatype.
Dan|||Thanks a lot. Exactly what I needed.
Shimon.
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:880E02E2-DA50-4466-9566-FC429802FB37@.microsoft.com...
> Hi Shimon
> you can use ISNUMERIC for this
> select isnumeric(@.s)
> For eg:
> if Value of @.s is '123' then the value returned is 1
> if Value of @.s is '123a' then the value returned is 0
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Shimon Sim" wrote:
>|||Shimon Sim,
Do you think it is enough using "like" operator?
Example:
select
cast(c1 as int)
from
(
select cast('1080' as varchar(10))
union all
select cast('atm' as varchar(10))
union all
select cast('1081' as varchar(10))
union all
select cast('atm' as varchar(10))
union all
select cast('atm' as varchar(10))
union all
select cast('1082' as varchar(10))
) as t1(c1)
where
c1 not like '%[^0-9]%'
AMB
"Shimon Sim" wrote:

> I need to check if string could be converted to a int without throwing any
> errors.
> I need to do something like this
> DECLARE @.s varchar(20)
> DECLARE @.i int
> --if following is possible
> @.i=CAST (@.s as int)
> --then
> SELECT @.1
> --else
> SELECT 0
> if string is not a number I really don't need to deal with it in the first
> place.
> The real life example of my scenario is checking uniqueness of check numbe
r
> for bank transactions. If user writes ATM for check number we don't need t
o
> check the uniqueness.
> Could it be done?
> Thanks,
> Shimon.
>
>|||Thank you for this note.
Shimon.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:OnUdojyoFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Oh, one thing I missed in my reply - if the string is numeric, but too
> large to fit into an int, you'll get an error, so you should have some
> check on the string length to determine if it'll fit, or cast into the
> largest numeric datatype.
> Dan
>|||I am not sure if it will work in my scenario.
Thank you
Shimon.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D57DFB0A-33B2-4239-AA77-BA15195B6789@.microsoft.com...
> Shimon Sim,
> Do you think it is enough using "like" operator?
> Example:
> select
> cast(c1 as int)
> from
> (
> select cast('1080' as varchar(10))
> union all
> select cast('atm' as varchar(10))
> union all
> select cast('1081' as varchar(10))
> union all
> select cast('atm' as varchar(10))
> union all
> select cast('atm' as varchar(10))
> union all
> select cast('1082' as varchar(10))
> ) as t1(c1)
> where
> c1 not like '%[^0-9]%'
>
> AMB
> "Shimon Sim" wrote:
>

Charts don't update when using dropdown parameters

I have recently upgraded from RS2000 to RS2005. The reports seem to have
converted without any errors. In the report designer, everything works fine.
When deploying to the server the reports don't update properly.
Details:
The reports have two components (a table and a chart) - both are based on
the same query. There are three parameters in a report. One is a dropdown
box that includes a list of products based on a subquery. The other two
parameters are textboxes that provide start and end months for the reporting
period. When changing either of the month parameters, the table and chart
updates with proper data. When changing the product (dropdown box), the
table information updates, but the chart does not (it still includes the
title and data from the previous update when a month was changed; or it
includes the results of the first query if a month was not changed).
Has anyone experienced anything like this? Is there a fix?Hello Piell,
I tested on my side and do not reproduce this issue.
Also, I did not find any related issue in our internal database.
I would like to know if this issue appeared on all reports?
If you apply SP1 on your SSRS 2005, does this issue persist?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Wei,
We have the same problem on all reports and we have even deleted the chart
and re-created it and get the same results. I will be checking with the
admins later today to find out if SP1 has been applied. If it hasn't, we
will have them update it.
Thanks.|||Wei,
We have installed SQL 2005 SP1 and still have the same problem. Would the
available hot fix help
http://download.microsoft.com/download/6/e/8/6e85f7ab-9f6c-4f3c-8f89-da0f78e026dc/rs2005-kb918222-x86-enu.exe|||We installed hot fix and charts still don't update when dropdown parameter
changes.|||Hello piell,
I would like to suggest you to check your IE security setting and try to
set the security level to low.
If this issue still persist, would you please send a sample report to me so
that I could try to reproduce this issue on my side.
To reach me, please remove the ONLINE in my email address.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Character set support

I'm looking at an MS SQL server database and it stores city names
across the world. All a
arabic , chinese etc names are converted to the latin alphabet.

I feel it is probably critical we support accents as a means to
distinguish between cities that would be converted to the same Latin
alphabet representation otherwise and as a matter of cultural
politeness.

Now ..... how do we do this?
Use varchar? which character code page do we use?
Use nvarchar? I heard a rumour that SQL server pads unused characters
with blanks and hence will use space unnecessarily.

The data will be presented on sheets and emailed around the world - it
will also appear on a website.

Any advice greatly appreciated - thanks
Oliver(oraustin@.hotmail.com) writes:

Quote:

Originally Posted by

I'm looking at an MS SQL server database and it stores city names
across the world. All a
arabic , chinese etc names are converted to the latin alphabet.
>
I feel it is probably critical we support accents as a means to
distinguish between cities that would be converted to the same Latin
alphabet representation otherwise and as a matter of cultural
politeness.
>
>
Now ..... how do we do this?
Use varchar? which character code page do we use?
Use nvarchar?

You need to use nvarchar. With varchar you can only support one code
page, and that is not enough. For instance, if you use CP-1252, Windows
Latin-1, you will not be able to enter the capital of Roumania
correctly, nor the largest city of Turkey. (I'm using a newsreader that
is not Unicode capable, so embarrassing enough, I cannot enter these
name correctly myself.)

Quote:

Originally Posted by

I heard a rumour that SQL server pads unused characters
with blanks and hence will use space unnecessarily.

That is incorrect. If you use varchar/nvarchar, SQL Server will not
pad. However, if you save data with trailing spaces, SQL Server will
by default to chop that off. The char/nchar data types on the other
hand are fixed-length, but you would not use them for city length.

On the other hand, in nvarchar each character takes up two bytes, in
difference to varchar with one byte per character. So nvarchar takes
twice the space.

But given your requirements, you don't have much choice.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Character Conversion

Hi,

I'm using an ETL tool called DecisionStream.

On a particular SQL Server, when it performs an insert, the degree character is being converted to a stacked bar:

source data: 1332 NS 4 Tall 32 oz
after insert: 1332 NS 4 Tall 32 oz

This works fine on my laptop with SQL Server, but for some reason, on the SQL Server at work, this behaviour occurs.

Any input would be great.

thanks,
d.you could use the ascii and char() functions to check the value of the character on each computer.
then check the differences between the server and your laptop's regional settings in control panel > regional settings.
also check the character set\code page that SQL is loaded with in both instances, there should be a discrepancy in one of those two locations.
if not check for gremlins.

example
select ASCII('')

select Char(176)

select ASCII('|')

select Char(124)

Books Online {String Functions}|||I'd also check the column collations, and verify that the data path (including any temp files, network filters, etc) all support Unicode. I've been bitten more than once by character set conversions.

-PatP|||just made a huge oversite...the delivery where the character conversion is happening is not INSERTing (as I originally thought)...it is actually performing a BCP delivery...

Perhaps this stuff would make sense to one of you guys, as I don't know this utility very well.

TARGET TABLE DDL:

CREATE TABLE "dbo"."F_conv"
(
"col1" VARCHAR(21) NOT NULL
)
;

The .fmt file DecisionStream creates:

8.0
1
1 SQLCHAR 0 30 "\r\n" 1 col1 ""

the .dat file

1332 NS 4 Tall 32 oz

and the command:

bcp "MY_DB"."dbo"."F_conv" in "mydata.dat"
-q -S<server> -Usa -Psa -f"myformat.fmt"

thoughts? Tweaks to that fmt?|||Strictly as a test, try using:CREATE TABLE "dbo"."F_conv"
(
"col1" NVARCHAR(21) NOT NULL
)
;-PatP|||changing it to NVARCHAR delivered:

1332 NS 4░ Tall 32 oz|||can anyone successfully load via BCP the character:

actually...i'm going to start a new thread for this...