Check String as having Money Format

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

SQL Server 2000 Forums

Transact-SQL (2000)

Check String as having Money Format

Author

Topic

cjpsx
Starting Member

34 Posts

Posted - 2006-07-20 : 15:33:20

I have a string field which I need to verify is in a money format, ie 4.00.

I had been using NOT LIKE '%[0-999]%[.]%[0-99]' but this allows for alpha characters where the wildcard(%) is. I removed the wildcards '[0-999][.][0-99]' but now this pulls valid values. Is there a solution? I would use the ISNUMERIC function but I only want two places after the decimal.

Thanks,
ck

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2006-07-20 : 16:07:04

create table #tmp (
c1 varchar(10)
)

insert into #tmp values ('123.34')
insert into #tmp values ('12.34')
insert into #tmp values ('1.34')
insert into #tmp values ('1234.34')
insert into #tmp values ('$123.34')
insert into #tmp values ('1a23.34')
insert into #tmp values ('123.4')
insert into #tmp values ('123.')
insert into #tmp values ('123')
insert into #tmp values ('123.123')
insert into #tmp values ('123.1234')

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_CheckMoney]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_CheckMoney]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION fn_CheckMoney (@a varchar(1000)) RETURNS int
AS
BEGIN
declare @x as int, @y int, @return as int
select @x = len(@a), @y=1, @return = 0
while @y < @x
BEGIN
if substring(@a,@y,1) not in ('1','2','3','4','5','6','7','8','9','0',',','.')
BEGIN
set @return = 1
END
set @y = @y + 1
END
if CHARINDEX ( '.' ,@a, 0) not in (@x-1, @x-2)
BEGIN
set @return = 1
END
return @return
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

select * from #tmp where dbo.fn_CheckMoney(c1) = 1

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2006-07-20 : 18:39:01

Another way to skin the same cat...

CREATE FUNCTION fn_VerifyMoney (@Val varchar(1000)) RETURNS int
AS
begin
declare @DecPos int
declare @ret int

if isnumeric(@Val) = 1
begin
set @DecPos = charindex('.',@Val)
if @DecPos > 0
if len(substring(@Val,@DecPos+1,3)) <= 2
set @ret = 1
else
set @ret = 0
else
set @ret = 1
end
else
set @ret=0
return @ret
end

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-20 : 21:28:19

This seems to cover most cases.


declare @tmp table (c1 varchar(10))

insert into @tmp 
select c1 ='123.34'  union all
select c1 ='12.34'  union all
select c1 ='1.34'  union all
select c1 ='1,234.34'  union all
select c1 ='1,2,34.34'  union all
select c1 ='$123.34'  union all
select c1 ='1a23.34'  union all
select c1 ='123.4'  union all
select c1 ='123$.'  union all
select c1 ='123'  union all
select c1 ='123.123'  union all
select c1 ='123.123466' 

select
	C1,
	GoodMoney =
		convert(numeric(18,2),convert(money,
		case
		when C1 is null
		then null
		when isnumeric(C1) <> 1
		then null
		when c1 like '%[^0-9,.$]%' 
		then null
		when convert(money,C1) <> round(convert(money,C1),2)
		then null
		else c1
		end))
from
	@tmp

Results:



(12 row(s) affected)

C1         GoodMoney            
---------- -------------------- 
123.34     123.34
12.34      12.34
1.34       1.34
1,234.34   1234.34
1,2,34.34  NULL
$123.34    123.34
1a23.34    NULL
123.4      123.40
123$.      NULL
123        123.00
123.123    NULL
123.123466 NULL

(12 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources