May 5, 2005 at 1:10 am
Hi,
I want to use a between statement to get data from a varchar field where Referance numbers is stored. Most of the varchars are numbers, but some of them has an 'AF' in front. I used the statements:
select refno from SABPP.SABPP_CRMCLT Where refno not in ('A%') AND RefNo Between 10 AND 100 ORDER BY REFNO
then I tried to insert the values in a temp table
Create Table #Temp(
RefNo int NOT NULL
)
Insert Into #Temp(RefNo)
select refno from Table Where refno not in ('A%')
But still I get the error: cannot convert the value 'AF01' to int...
Help...
Anyone..?
May 5, 2005 at 4:59 am
Have you tried explicit conversion to int before storing/retrieving ?!
eg: select cast(left(refno, 2) as integer) ?!
1) Identify all rows where refno NOT LIKE 'A%'
2) Convert remaining rows to int value
3) Insert into temp table
4) Query
**ASCII stupid question, get a stupid ANSI !!!**
May 5, 2005 at 9:24 am
select refno from SABPP.SABPP_CRMCLT
Where refno not LIKE 'A%' ....
May 19, 2005 at 10:46 am
Use the isnumeric function, to select only those reference numbers that can be converted to numbers.
Insert Into #Temp(RefNo)
select refno from Table
where isnumeric(refno) = 1
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy