Convert Varchar field with spaces to 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 2008 Forums

Transact-SQL (2008)

Convert Varchar field with spaces to decimal

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-01-13 : 15:40:11

I have a column with data like this:

'1    678901'

with 4 spaces between the 1 and the 6. Could vary.

What I have to do is:
1) take the LEFT(1) character.
2) concatenate a decimal to it.
3) take the 2nd through 11th character and trim the spaces
4) concatenate this string to the 1st column and the decimal.
5) all the while making sure that this VARCHAR column has only numbers
6) making sure that there is not ALL spaces in this column including the first column.
7) converting any non-numeric result into either a ZERO or NULL.
8) inserting this into a DECIMAL(24,2) column.
9) In this case the result should be 1.678901
10)I have tried numerous things and it ends up failing trying to insert bad data, especially a space in the left into a number column.
I have done it successfully so far except when both columns are spaces.

This VB Script snippet does it, but it is not being used anymore
and is being done in T-SQL:

DTSDestination("Col004") = ScrubNumber(Mid(DTSSource("Col004"),1,1)  
	+  "."  
	+  Trim( Mid(DTSSource("Col004"),2,11)))

Function ScrubNumber (ColData)
	IF Not( IsNumeric(ColData)) or IsNull(ColData) or isempty( ColData)  THEN
		ScrubNumber = "0"
	ELSE
		ScrubNumber = ColData
	END IF
End Function

Thank you.

Duane

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-13 : 20:21:38

Are you using Microsoft SQL Server? I am asking because MID is not a T-SQL function. If you are using another DBMS, this forum - which is SQL Server specific - is not good for you. You probably would get better and faster answers at place like dbforums.com.

If you are using SQL Server, you can do what you described like this:

SELECT 
	CAST(	
		CASE 
			WHEN REPLACE(DTSSource,' ','') LIKE '%[^0-9]%' THEN NULL
			ELSE NULLIF(STUFF(REPLACE(DTSSource,' ',''),2,0,'.'),'.')
		END 
	AS DECIMAL(24,2));

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 23:50:12

As OP says posted code is VB function. the T-SQL Equivalent would be


...
CAST(CASE WHEN REPLACE(Col004,' ','') LIKE '%[^0-9]%' THEN 0 ELSE LEFT(REPLACE(Col004,' ',''),1) + '.' + STUFF(REPLACE(Col004,' ',''),1,1,'') END AS Decimal(24,2))
...

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

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-01-17 : 12:35:41

Thank you. I don't know which one is better, if either, but this does help me solve the problem. I am using SQL Server and this function was in SQL Server 2000 (DTS), which I thought was VB Script and not regular VB. Anyway, I am going to SQL Server 2008 transferring the flat file with SSIS and just wanted to do the function in T-SQL on SQL Server 2008 instead of a script component in SSIS. Thanks again.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources