SQL query with cast

Well, that's because your column value has an 'A' on it.
Do you want to perform the operation only in fields with numeric value? If so, then try this:

SELECT [Date], cast(Ref as int) as Ref,[Regarding],[SentBy],Corres_UID                     
FROM CORRES
WHERE (DeleteRecord is null or DeleteRecord = 0) AND ISNUMERIC(Ref)
ORDER BY Ref Asc

Select allOpen in new window

Vitor,
Got an error:

An expression of non-boolean type specified in a context where a condition is expected, near 'ORDER'.

Hi,
Please try this ..

SELECT [Date], cast(Ref as int) as Ref,[Regarding],[SentBy],Corres_UID                     
FROM CORRES
WHERE (DeleteRecord is null or DeleteRecord = 0) AND Ref LIKE '%[0-9]%'
ORDER BY Ref Asc

Select allOpen in new window

Try this also-

SELECT [Date], cast(Ref as int) as Ref,[Regarding],[SentBy],Corres_UID                     
FROM CORRES
WHERE (DeleteRecord is null or DeleteRecord = 0) AND Ref NOT LIKE '%[^0-9]%'
ORDER BY Ref Asc

Select allOpen in new window

Cheers!

RIAS, did you really try Pawan's solution?
It won't work for you.

Hi Rias ,

IsNUMERIC is not full proof. Vitor's solution will FAIL in below case-

See below-

/*------------------------
SELECT ISNUMERIC('$')     
------------------------*/

-----------
1

(1 row(s) affected)

Select allOpen in new window

We don't know her data. I just asked if she tested it.
What I know is that it will fail Pawan's solution for the give example.
If it will fail for mine solution, please let me know which data failed.

Let the author decide then.

Vitor,
Your solution worked perfectly!

Pawan,
i don't have any refnos starting with $ and I have tested yours as well it does work as well .
But as i said I don't have any ref starting with wildchars so,Vitor's is more appropriate.

Cheers

RIAS, Pawan's solution can't work because it excluding numerics: "Ref NOT LIKE '%[^0-9]%'"

Experts i have a question for you,

Pawan query resulted in 39750 rows
Vitor 's query resulted in  39753 rows

But, actual count of the table is 39775

Don't understand where is the problem

Vitor,

Select count(*) from CORRESPONDENCE 	WHERE  ISNUMERIC(Ref)=0 

Select allOpen in new window

Return 22 rows.
So,Vitor your query returns  39753 rows  and table count is  39775

Can you please explain why i am not getting   39775

You need to provide the data and where are the differences.
Again, for the example you provided, Pawan's solution will give error:

CREATE TABLE #table1 (
	MyField varchar(30) )

insert into #table1 
values ('2455A')

SELECT CAST(MyField AS INT)
FROM #table1
WHERE MyField LIKE '%[^0-9]%'

------------------------------------------------------------------------------------------------------------------
Msg 245, Level 16, State 1, Line 30
Conversion failed when converting the varchar value '2455A' to data type int.

Select allOpen in new window

So you should explain how you got it working.

Pawan's query :

SELECT [Date], cast(Ref as int) as Ref,Auth,FaxAuth,Attachments,FaxNo,Attn,FaxLetterMemo,[To],[Regarding],[SentBy],Correspondence_UID                     
					FROM CORRESPONDENCE 
					WHERE (DeleteRecord is null or DeleteRecord = 0) AND Ref NOT LIKE '%[^0-9]%' ORDER BY Ref Asc

Select allOpen in new window

It returned 39750 rows.
i don't why you are saying it won't work it has worked but not returning correct number of rows. But,as i investigate further even your query is not returning all the rows.

I'm not expecting this to return all rows. The idea is to convert the ones that are convertible (only numbers in the field).
Can you provide the following result?

Select Ref 
from CORRESPONDENCE 	
WHERE  ISNUMERIC(Ref)=0 

Select allOpen in new window

yes,you are correct there is data like :

7597F
7598F
2455A
4809f
5834f
6238`
6599*

But the count is 22.Then your query should return  39750- 22 =39728 but your query is returning  39753 ?

How can my query return more rows that exists in the table?
Please review the numbers.

Hi Rias,
Please try this-

AND TRY_CAST(@sampletext AS int) IS NOT NULL

--

SELECT [Date], cast(Ref as int) as Ref,[Regarding],[SentBy],Corres_UID                     
FROM CORRES
WHERE (DeleteRecord is null or DeleteRecord = 0) 
AND TRY_CAST(@sampletext AS int) IS NOT NULL
ORDER BY Ref Asc

--

Select allOpen in new window

Hope it helps!

Sorry,

Count of table is

39775

Pawan,
What is    "@sampletext".

Updated...

SELECT [Date], cast(Ref as int) as Ref,[Regarding],[SentBy],Corres_UID                    
FROM CORRES
WHERE (DeleteRecord is null or DeleteRecord = 0)
AND TRY_CAST(Ref AS int) IS NOT NULL
ORDER BY Ref Asc

So, it's ok then: 39775- 22 =39753
Isn't that what my query returned?

Perfect!!!
Sorry Pawan ,your query is not working.
So,i am requesting moderator to accept the correct solution.

Which one is not working ? Last one?

yes

The query should return 39753  and your query is returning 39750

You should check again.. also provide me data for which it is not working.? Would like to check.

My solution should work. you are doing something incorrect. meaning you are getting extra rows. See yourself. I dont care about points. I have many.

Pawan,
I have checked it .

7597F
7598F
2455A
4809f
5834f
6238`
6599*

But the count is 22.Then your query should return  39753.

39775- 22 =39753

so below are INT ?

7597F
7598F
2455A
4809f
5834f
6238`
6599*

Nope

Can you give a value for which my solution not working ?

Value?

Your query returns count of 39750.
It should return 39753 as :

The ref present in the table are:

7597F
7598F
2455A
4809f
5834f
6238`
6599*

the count of ref which are not numeric is 22.Then your query should return  39753.
The total count of the table is 39775.
So the result should be

39775- 22 =39753

Pawan,
Sorry beyond this can't offer any explanation.
Really appreciate your help.

No problem RIAS. Actually we always wanted to go deep to understand things in detail and here I wanted to understand whats going on.
No problem, You can continue your work.
Just a request, can you please attach the data for the entire column and send it as an attachment. Thank you.

Pawan,

Thanks due to confidentiality can't attach data.but the ref is like :


7597F
7598F
2455A
4809f
5834f
6238`
6599*

653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
2
3
4
5
6
7
8
9

Cheers