How to find all values in a column which is not money

Hi,

How to find all values in a column which is not money datatype
RIASAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Looks like you want the opposite (not money). So just change the filter:
SELECT *
FROM TableName
WHERE ISNUMERIC(ColumnName) = 0

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Try..

--

SELECT [col_names] FROM TABLENAME 
WHERE COLNAME NOT LIKE '%.%' AND COLNAME NOT LIKE '%.00%' 

--

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
How to find all values in a column which is not money datatype
What's the column data type then?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RIASAuthor Commented:
It is nvarchar(50)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Wondering why are you storing money in a nvarchar field.
So, how's those money values formatted in that column?
1
 
RIASAuthor Commented:
Thats the point i ma changing the datatype but, need to know the data which is not in money format
0
 
Pawan KumarDatabase ExpertCommented:
Try..

--

CREATE TABLE a
(
	a VARCHAR(100)
)
GO

INSERT INTO a VALUES ('2.34'),('5.0'),('5'),('13.2'),('Pawan'),('13.2')
GO

SELECT * FROM a 
WHERE a NOT LIKE '%.%' AND a NOT LIKE '%.00%' 

--

Open in new window


Output
---------
a
5
Pawan
0
 
RIASAuthor Commented:
Pawan,
 There is aproblem with you query its returning even
10900
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Thats the point i ma changing the datatype but, need to know the data which is not in money format
For help you I need to know how a money value is stored in that field. Can you provide a sample?
0
 
Pawan KumarDatabase ExpertCommented:
Hi Rias,
Have you tried the code I gave. <<Check out the example One>>

Regards,
Pawan
0
 
RIASAuthor Commented:
Vitor,

Example:

NULL
$43,869.60
£10,000.00
£235,847.99
£266,941.14
£486,704.27
1, 029.000.00
1, 603, 571.10
1,000,000.00
1,055.00
1,066,069.80
1,100.00
1,209,454.44
1,250.00
1,277,877.27
1,328,837.00
1,436,880.00
1,457.50
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You should mark which ones are considered money or we just assume everything that has numbers are money?
0
 
RIASAuthor Commented:
yes, we just assume everything that has numbers are money
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Try

[code]
--

CREATE TABLE a
(
	a VARCHAR(100)
)
GO

INSERT INTO a VALUES 
(NULL),
 ('$43,869.60'),
('£10,000.00'),
('£235,847.99'),
('£266,941.14'),
('£486,704.27'),
 ('1, 029.000.00'),
 ('1, 603, 571.10'),
 ('1,000,000.00'),
 ('1,055.00'),
 ('1,066,069.80'),
 ('1,100.00'),
 ('1,209,454.44'),
 ('1,250.00'),
 ('1,277,877.27'),
 ('1,328,837.00'),
 ('1,436,880.00'),
 ('1,457.50' )

GO

SELECT * FROM a 
WHERE a NOT LIKE '%.%' OR a IS NULL 

--

Open in new window


Output
---------
a
NULL

[/code]
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, then this should work:
SELECT *
FROM TableName
WHERE ISNUMERIC(ColumnName) = 1

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Hi Rias,

Any luck ?
0
 
RIASAuthor Commented:
Thanks!
0
 
Pawan KumarDatabase ExpertCommented:
@Rias - You cannot accept Incorrect solution. His both solutions are giving incorrect outputs. Please note that these solution will be referred by people in future.

Both see below -


SELECT *
FROM a
WHERE ISNUMERIC(a) = 0

O/p

NULL
1, 029.000.00
1, 603, 571.10


SELECT *
FROM a
WHERE ISNUMERIC(a) = 1

Output
------

$43,869.60
£10,000.00
£235,847.99
£266,941.14
£486,704.27
1,000,000.00
1,055.00
1,066,069.80
1,100.00
1,209,454.44
1,250.00
1,277,877.27
1,328,837.00
1,436,880.00
1,457.50
0
 
Pawan KumarDatabase ExpertCommented:
Hi Rias,

Can you confirm whether Vitor Solution worked for you and how ?

Thank You in advance.

Regards,
Pawan
0
 
RIASAuthor Commented:
Pawan,
Just give sometime.Will get back on this .
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Those aren't numbers:
1, 029.000.00 --> Have an extra space and 3 dots
1, 603, 571.10 --> Have an extra space

If the author want to transform those values into a money field will fail.
So Pawan's solution won't work if let these values stays in the table.

Capture.PNG
3
 
Pawan KumarDatabase ExpertCommented:
Okies. Cool. !!
0
 
RIASAuthor Commented:
Thanks Vitor!!!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your welcome, RIAS.
Following Pawan's comment "@Rias - You cannot accept Incorrect solution." you should unaccept his solution. A couple of days ago you also gave him points for his "effort" (copying my solution and change a value) and that was also an incorrect solution.
I won't be picky with this (will let a moderator to decide what to do) but you should be more careful with the answers you're accepting. Sometimes is good to perform more tests before accepting immediately an answer. We aren't on hurry here. :)
Cheers
3
 
RIASAuthor Commented:
Vitor,

 Went on some other task so could not test thoroughly but,will take a note of this.

Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.