Can't convert from VarChar to Numeric.

  • (2 Pages)
  • +
  • 1
  • 2

28 Replies - 1114 Views - Last Post: 08 April 2016 - 11:55 AM

#1 jphoc13  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 06:04 AM

This is what my typical field looks like:

"+0000000.00000000"

I am trying to convert this from varchar(50) to decimal (10,4). But it is not allowing me to saying there is an error. Is there anyway to force this?

I am also likely going to be asking a few other SQL questions today so keeping this thread open for anything else I can't find answers for on Google. Newish to SQL....
Is This A Good Question/Topic? 0
  • +

Replies To: Can't convert from VarChar to Numeric.

#2 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 592
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 06:05 AM

what is the +

it has to be numeric value a + is not
Was This Post Helpful? 0
  • +
  • -

#3 jphoc13  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 06:19 AM

I think it means that the field has more 00000's....Or maybe not, I don't know.

So I guess I should do a query that removes any + from the fields. Then do the conversion?

Ok I have the + removed but it still won't let me convert. My guess is that it is because it has more than 10 characters, which is what I am trying to convert to.

This post has been edited by jphoc13: 06 April 2016 - 06:12 AM

Was This Post Helpful? 0
  • +
  • -

#4 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 592
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 06:21 AM

yes-- but what happens if the data has more than (10,4)


usually if the data has more than 10 at the beginning it will either truncate it or error out-- the same with the 4
Was This Post Helpful? 0
  • +
  • -

#5 jphoc13  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 06:32 AM

Yeah it still won't let me convert. So I am onto removing all those zeroes.

I am trying to update the table but it is giving me an error saying it expects a "Set' and not a "select". So I tried set and it gave me even more errors.

Update ClearingFutures
SELECT
SUBSTRING(Strike, PATINDEX('%[^0 ]%', strike + ' '), LEN(strike))
FROM ClearingFutures

This post has been edited by jphoc13: 06 April 2016 - 06:35 AM

Was This Post Helpful? 0
  • +
  • -

#6 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 592
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 06:45 AM

Update ClearingFutures
SELECT
SUBSTRING(Strike, PATINDEX('%[^0 ]%', strike + ' '), LEN(strike))
FROM ClearingFutures 


should be something like

Update ClearingFutures
set yourColumnNameGoesHere = 
(SELECT
SUBSTRING(Strike, PATINDEX('%[^0 ]%', strike + ' '), LEN(strike))
FROM ClearingFutures )


add a where clause if needed
Was This Post Helpful? 0
  • +
  • -

#7 jphoc13  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 06:57 AM

Thanks! Your code got rid of the syntax errors but now I get that the:

"Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

From what I understand it should always return one value for each row? Or am I missing something here?

Update ClearingFutures
Set Strike =
(SELECT
SUBSTRING(Strike, PATINDEX('%[^0 ]%', strike + ' '), LEN(strike))
FROM ClearingFutures)

This post has been edited by jphoc13: 06 April 2016 - 06:58 AM

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13492
  • View blog
  • Posts: 53,895
  • Joined: 12-June 08

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 07:07 AM

The error is fairly clear - your SELECT is returning more than one row. An update of a cell cannot have many rows.

You have no 'where' to limit it to one row.. or a distinct.
Was This Post Helpful? 0
  • +
  • -

#9 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 592
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 07:08 AM

if you are updating a table from another table data you need to do it something like this:

UPDATE customers
SET city = (SELECT city
            FROM suppliers
            WHERE suppliers.supplier_name = customers.customer_name)
where clause here


and to elaborate on Modi's post you would need to put the key word distinct in the select clause like
select distinct columnName

This post has been edited by DarenR: 06 April 2016 - 07:10 AM

Was This Post Helpful? 0
  • +
  • -

#10 jphoc13  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 07:12 AM

Ok I guess I am confused on what this update is doing then. My intent is to go through each row in the column strike and remove zeroes. So I thought I was doing a 1 to 1 value return, but you guys are saying it is doing all the rows and trying to update the first row?
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13492
  • View blog
  • Posts: 53,895
  • Joined: 12-June 08

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 07:29 AM

No.

When

In the table, set the column value to a value. No 'WHERE' means all the rows.
1	Update ClearingFutures
2	Set Strike =


Here - this can be more than one row of data. You cannot set a single row in the update to many rows of data. Literally draw out each update on paper if you have to see how it works.
3	(SELECT
4	SUBSTRING(Strike, PATINDEX('%[^0 ]%', strike + ' '), LEN(strike))
5	FROM ClearingFutures)


row 1
SELECT brings back 1 and 2
tries to set 'Strike' to values 1 or 2. Error!


If you want that current row to be updated then I would suggest telling it so:
Update ClearingFutures
Set Strike = SUBSTRING(Strike, PATINDEX('%[^0 ]%', strike + ' '), LEN(strike))


You should probably sit back down with a Relational Database book and brush up on this. How UPDATE works versus SELECT are fairly crucial bits of info.
Was This Post Helpful? 0
  • +
  • -

#12 jphoc13  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 07:33 AM

Ok that makes sense now, sorry for the misunderstanding, do not use select when updating all the rows, makes sense.
Was This Post Helpful? 0
  • +
  • -

#13 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13492
  • View blog
  • Posts: 53,895
  • Joined: 12-June 08

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 07:43 AM

Well, no. Not always. You need to know what you are UPDATING, what values, etc. Again.. reading over relational databases would clear this up.

Example:


cre ate table #foo(lVal int)
cre ate table #bar(lVal int, zVal int)

ins ert into #foo(lVal) VALUES (1)
ins ert into #foo(lVal) VALUES (1)
in sert into #foo(lVal) VALUES (1)


ins ert into #bar(lVal,zVal) VALUES (10, 50)
ins ert into #bar(lVal,zVal) VALUES (20, 51)
ins ert into #bar(lVal,zVal) VALUES (30, 52)

SELECT *
FROM #foo

UP DATE #foo
SET lVal = (SELECT zVal 
			FROM #bar
			WHERE lVal = 30)

SELECT *
FROM #foo

dr op table #foo
dr op table #bar


lVal
-----------
1
1
1

lVal
-----------
52
52
52

Was This Post Helpful? 1
  • +
  • -

#14 jphoc13  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 08:27 AM

Thank you for you help guys, learned a good amount. I have another query that is giving me a syntax error on a period, where I enlarged the text.

As I am learning my intent is often different from what I am actually telling SQL to do. All I want to do is set RivalAccountMap.Portfolio to ClearingFutures.AccountIdentifier, when ClearingFutures.AccountIdentifier = RivalAccountMap.Account. In other words doing a little conversion

Insert into ClearingNormalized(Account)
Select(AccountIdentifier) 
from ClearingFutures
Inner Join RivalAccountMap
on  ClearingFutures.AccountIdentifier = RivalAccountMap.Account
Set [color="#FF0000"][size="4"]ClearingFutures.AccountIdentifier[/size][/color] = RivalAccountMap.Portfolio
Group By RecordType, 
         FuturesCode, 
		 Exchange, 
		 PutCallCode, 
		 Strike,
         ExpirationDate, 
		 BuySell, 
		 AccountIdentifier


I also thought this query may work from samples I saw when googling:

Update ClearingNormalized

Set ClearingFutures.AccountIdentifier = RivalAccountMap.Portfolio
from RivalAccountMap, ClearingFutures
where RivalAccountMap.Account = ClearingFutures.AccountIdentifier


"The multipart identifier, ClearingFutures.AccountIdentifier, can not be bound."
Was This Post Helpful? 0
  • +
  • -

#15 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13492
  • View blog
  • Posts: 53,895
  • Joined: 12-June 08

Re: Can't convert from VarChar to Numeric.

Posted 06 April 2016 - 08:29 AM

INSERTS do not work that way. It is different from an UPDATE.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2