Convert var to numeric (decimal)

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 2005 Forums

Transact-SQL (2005)

Convert var to numeric (decimal)

Author

Topic

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-01-28 : 09:12:37

Hello,

How do I convert the varchar to numeric to make datatype the same. Please see sample data below: Id_case=Case. However, "Case" is varchar datatype, and ID_case is number. Please show me how to convert that. Thank you!

Table 1: Field "Case"
Case Varchar(50)
002261556 S (R-Crd)
0612588 S(IssueC)
01111589-Rcredit
02204798(C-1393)
0059850 (0-R)
00606020 (1393)
001264018 (O-R)
0001851245 (O-R)
001812570 (O-R)
002351312(O-R)
001289822
001734454

Table2: Field "ID_CAse"
ID CASE (Numeric (decimal)
2261556
612588
1111589
2204798
59850
606020
1264018
1851245
1812570
2351312
1289822
1734454

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:17:32

[code]SELECT CAST(LEFT(REPLACE(REPLACE(Case,'(',' '),'-',' '),CASE WHEN CHARINDEX(' ',REPLACE(REPLACE(Case,'(',' '),'-',' '))>0 THEN CHARINDEX(' ',REPLACE(REPLACE(Case,'(',' '),'-',' '))-1 ELSE LEN(REPLACE(REPLACE(Case,'(',' '),'-',' ')) END) AS int) FROM Table[/code]

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-01-28 : 09:31:04

quote:

Originally posted by visakh16

SELECT CAST(LEFT(REPLACE(REPLACE(Case,'(',' '),'-',' '),CASE WHEN CHARINDEX(' ',REPLACE(REPLACE(Case,'(',' '),'-',' '))>0 THEN CHARINDEX(' ',REPLACE(REPLACE(Case,'(',' '),'-',' '))-1 ELSE LEN(REPLACE(REPLACE(Case,'(',' '),'-',' ')) END) AS int) FROM Table

I still get syntax error. I am trying to work it out now...Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:34:04

whats the error. show your query?

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-01-28 : 12:28:52

I used VAL FUNCTION in visual basic, and it worked well.

thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 12:36:11

quote:
Originally posted by nt4vn

I used VAL FUNCTION in visual basic, and it worked well.

thanks,

were you asking to do this in sql reports?

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-01-28 : 13:00:43

quote:
Originally posted by visakh16

quote:
Originally posted by nt4vn

I used VAL FUNCTION in visual basic, and it worked well.

thanks,

were you asking to do this in sql reports?

Yes, at first I tried to do in SQL report, but I have to join another table from different server, then I do in access...and from there I can use VAL FUNCTION ...and it work. Your replace function is work too in SQL, but will not work in access as I joined two server together. Thanks for giving me a hint...

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-01-28 : 13:02:01

Here's a function that works in all cases:

 
CREATE FUNCTION [dbo].[udf_StripNonIntegers]
	(@string as varchar(8000))
RETURNS  varchar(8000)
AS
BEGIN
	declare @stripped as varchar(8000)
	declare @i as int
	declare @result as varchar(8000)
	declare @chr as char(1)
	
	set @i = 1
	set @stripped = ''
	
	while @i <= len(@string)
	begin
		set @chr = substring(@string, @i, 1)
		if @chr like ('[0-9]') 
		begin
			set @stripped = @stripped + @chr
		end	
		set @i = @i + 1
	end
	
	return @stripped
END
GO

-- Example usage:
	SELECT CAST([dbo].[udf_StripNonIntegers](Case) AS int) AS Case
	FROM Table1 
	WHERE....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 13:04:39

no problem...

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-01-28 : 13:10:45

quote:

Originally posted by tonymorell10

Here's a function that works in all cases:

 
CREATE FUNCTION [dbo].[udf_StripNonIntegers]
	(@string as varchar(8000))
RETURNS  varchar(8000)
AS
BEGIN
	declare @stripped as varchar(8000)
	declare @i as int
	declare @result as varchar(8000)
	declare @chr as char(1)
	
	set @i = 1
	set @stripped = ''
	
	while @i <= len(@string)
	begin
		set @chr = substring(@string, @i, 1)
		if @chr like ('[0-9]') 
		begin
			set @stripped = @stripped + @chr
		end	
		set @i = @i + 1
	end
	
	return @stripped
END
GO

-- Example usage:
	SELECT CAST([dbo].[udf_StripNonIntegers](Case) AS int) AS Case
	FROM Table1 
	WHERE....

Thank you!

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-01-28 : 13:18:25

Your welcome :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources