Convert text to number

Is there any way to convert a text field to a number field and drop any data
that has an alpha character in it? I am able to override the message in
Access but I am not sure if you can in SQL.
For ex.
"1234AB" Don't want this data
"123456" Want this data
Thanks,
LisaIf the numbers are positive integers, then try:
use northwind
go
create table t (
colA varchar(25)
)
go
insert into t values('1234AB')
insert into t values('123456')
go
delete t where patindex('%[^0-9]%', colA) > 0
go
alter table t
alter column colA int
go
select * from t
go
drop table t
go
if not, use the functions from this link.
What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"Barce5" wrote:

> Is there any way to convert a text field to a number field and drop any da
ta
> that has an alpha character in it? I am able to override the message in
> Access but I am not sure if you can in SQL.
> For ex.
> "1234AB" Don't want this data
> "123456" Want this data
> Thanks,
> Lisa

Convert from money to varchar?

I want to print in an error message a money value but have to convert it to a varchar first. I do not have any clue how to do this. Could someone help me out?

SELECT @.iError = @.@.error, @.iRowCount = @.@.rowcount
IF(@.iError <> 0 OR @.iRowCount <> 1)
BEGIN
CONVERT(@.dValue, @.cValue)
set @.cError = 'Error attempting to insert new record.\n\t\tProduct : ' + @.cProduct + '\n\t\tSub-account : ' + @.cSubAccount + '\n\t\tCost : $' + @.cValue
RAISERROR(@.cError, 16, 1)
END

@.cValue is a varchar(20)
@.dValue is money

Any help?

Mike BI tried the following

SELECT CAST(@.dValue, AS VARCHAR) AS "cValue"

but I get the error "Error 156 : Incorrect syntaxt near keyword 'AS'"

Mike B|||I figured it out. I used the CAST

set @.cError = ............ + '\n\t\tCost : $' + CAST(@.dValue AS VARCHAR)

Mike B|||Originally posted by MikeB_2k4
I want to print in an error message a money value but have to convert it to a varchar first. I do not have any clue how to do this. Could someone help me out?

SELECT @.iError = @.@.error, @.iRowCount = @.@.rowcount
IF(@.iError <> 0 OR @.iRowCount <> 1)
BEGIN

Hey...looks vaguely familiar...

And I think you need an AND instead...

Why not break it up in to 2 clauses though...

That way you'll know it 1 of 2 types of errors...the first a system problem and the second a user error...

It's more coding, but you'll never be in doubt|||Originally posted by Brett Kaiser
Hey...looks vaguely familiar...

hehe, you didn't copyright it I hope? :)

And I think you need an AND instead...

I think OR is required because I am expecting only 1 row to be inserted. Therefore, if more or less then one row?

Why not break it up in to 2 clauses though...

Good Suggestion.

Thanks
Mike B

P.S., I will try to remember to give credit where credit is due next time I copy some of your code! ;)|||Well if @.@.Error = 0 and @.@.ROWCOUNT = 2, then your statement would be true, no?|||Originally posted by Brett Kaiser
Well if @.@.Error = 0 and @.@.ROWCOUNT = 2, then your statement would be true, no?

No, I don't even no why I am checking rowcount. Maybe just to confuse myself. The stored procedure inserts a record and only a single record.

I should really only be checking the error value probably, but due to lack of experience, I still don't trust the insertion, deletion etc.. statements in a SQL statement.

I have a habit of checking to ensure the record count is what is expected, nothing more, nothing less. Unnecessary but.....

Thanks for your input.

Mike B

In case your interested, here is the Stored proc

CREATE PROCEDURE usp_updateProductCost

@.dValue money,
@.cProduct varchar(10),
@.cSubAccount varchar(10)

AS

declare @.cError varchar(255), @.iError int, @.iRowCount int

if(EXISTS(SELECT * FROM tbProductCosting WHERE tbProductCosting.Product = @.cProduct AND tbProductCosting.SubAccount = @.cSubAccount))
BEGIN
Update tbProductCosting SET tbProductCosting.Cost = @.dValue WHERE tbProductCosting.Product = @.cProduct AND tbProductCosting.SubAccount = @.cSubAccount
SELECT @.iError = @.@.error

if(@.iError <> 0)
BEGIN
set @.cError = 'Error update cost for product ' + @.cProduct
RAISERROR(@.cError, 16, 1)
END
END
else
BEGIN
INSERT INTO tbProductCosting (Product, SubAccount, Cost) VALUES (@.cProduct, @.cSubAccount, @.dValue)
BEGIN
SELECT @.iError = @.@.error, @.iRowCount = @.@.rowcount
IF(@.iError <> 0 OR @.iRowCount <> 1)
BEGIN
set @.cError = 'Error attempting to insert new record.\n\t\tProduct : ' + @.cProduct + '\n\t\tSub-account : ' + @.cSubAccount + '\n\t\tCost : $' + CAST(@.dValue AS VARCHAR)
RAISERROR(@.cError, 16, 1)
END
END
END
GO|||I do see one major problem now if I look at it. Even if the record count was greater then 1, I don't handle it. I should be using transaction management. hmmmm

Mike B

CONVERT Error Message

Greetings,
I have a "newbie" question in relation to dates. I am trying to append the
year, month, and day to one another in order to create a new date. The SQL
I
am using is below. The error message I am receiving is "The conversion of a
char data type to a datetime data type resulted in an out-of-range datetime
value." I have also tried using "CAST", but I receive a similar error
message on that as well. Any idea as to what I am doing wrong?
Convert(varchar(12),(month(ebm.Hire_Date + 30) + 1)) + '/' +
Convert(varchar(12),day(ebm.Hire_Date + 30)) + '/' +
Convert(varchar(12),year(ebm.Hire_Date + 30))
Thanks in advance!
--
SherwoodSherwood (Sherwood@.discussions.microsoft.com) writes:
> I have a "newbie" question in relation to dates. I am trying to append
> the year, month, and day to one another in order to create a new date.
> The SQL I am using is below. The error message I am receiving is "The
> conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value." I have also tried using "CAST", but I
> receive a similar error message on that as well. Any idea as to what I
> am doing wrong?
> Convert(varchar(12),(month(ebm.Hire_Date + 30) + 1)) + '/' +
> Convert(varchar(12),day(ebm.Hire_Date + 30)) + '/' +
> Convert(varchar(12),year(ebm.Hire_Date + 30))
There are two safe date formats in SQL Server (three in SQL 2005). The
most commonly used is YYYYMMDD. When you use delimited formats, it's up
to the settings how the date will be interpreted.
Anyway, I am not really sure what you want to do, but you should have a
look at the dateadd() function, that may simplify your problem.
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|||I was able to resolve it by using the code below. The reason it initially
failed was due to the fact that I was using it in one of the conditions of a
CASE statement. The other condition had a different format and apparently
that executed first. At any rate, the code below seems to be working.
Convert(varchar(15),year(ebm.Hire_Date)) + '/' +
Convert(varchar(15),(month(ebm.Hire_date) + 1)) + '/' +
Convert(varchar(15),day(ebm.Hire_Date))
Thanks.
--
Sherwood
"Erland Sommarskog" wrote:

> Sherwood (Sherwood@.discussions.microsoft.com) writes:
> There are two safe date formats in SQL Server (three in SQL 2005). The
> most commonly used is YYYYMMDD. When you use delimited formats, it's up
> to the settings how the date will be interpreted.
> Anyway, I am not really sure what you want to do, but you should have a
> look at the dateadd() function, that may simplify your problem.
> --
> 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
>|||Sherwood (Sherwood@.discussions.microsoft.com) writes:
> I was able to resolve it by using the code below. The reason it
> initially failed was due to the fact that I was using it in one of the
> conditions of a CASE statement. The other condition had a different
> format and apparently that executed first. At any rate, the code below
> seems to be working.
> Convert(varchar(15),year(ebm.Hire_Date)) + '/' +
> Convert(varchar(15),(month(ebm.Hire_date) + 1)) + '/' +
> Convert(varchar(15),day(ebm.Hire_Date))
Hire someone in December, and you will get a nasty surprise.
This does work:
dateadd(MONTH, 1, ebm.Hire_date)
And this is what you should use.
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