|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
varchar data starts with numberI have a field in a table which is varchar data type. Some records in that field of the table starts with number such as 2AFX. When I try to retreive records for that field I do not get any records ( select * from table where field1='2AFX'). But I see records are there. I feel this will be a very simple fix and that is the reason I am not posting DDLs. I do highly appreciate your help as this is killing my time. Thanks a million in advance. best regards, Mamun Your SQL looks fine, and based on what you have said it should be working.
I am guessing that either there is something else going on (datatypes, hidden characters, collation, etc.) that you are not aware of, but the DDL and sample data may show. Supply these and let us know where you are executing this query (QA, EM, or some other tool). Show quote "mamun" <mamun***@hotmail.com> wrote in message news:1156524010.461385.180440@74g2000cwt.googlegroups.com... > Hi All, > > I have a field in a table which is varchar data type. > > Some records in that field of the table starts with number such as > 2AFX. > > When I try to retreive records for that field I do not get any records > ( select * from table where field1='2AFX'). But I see records are > there. > > I feel this will be a very simple fix and that is the reason I am not > posting DDLs. > > I do highly appreciate your help as this is killing my time. > > Thanks a million in advance. > > best regards, > Mamun > My guess is that there are some other unprintable characters in that column.
Try the following - replace ### with however long your field1 column is, that is, if field1 is varchar(25), use varbinary(25). That should let you see in hex exactly what is in that column. Select Cast(field1 As varbinary(###)), * from table Tom Show quote "mamun" <mamun***@hotmail.com> wrote in message news:1156524010.461385.180440@74g2000cwt.googlegroups.com... > Hi All, > > I have a field in a table which is varchar data type. > > Some records in that field of the table starts with number such as > 2AFX. > > When I try to retreive records for that field I do not get any records > ( select * from table where field1='2AFX'). But I see records are > there. > > I feel this will be a very simple fix and that is the reason I am not > posting DDLs. > > I do highly appreciate your help as this is killing my time. > > Thanks a million in advance. > > best regards, > Mamun > Hi,
When I tried using your code, I got the following. 0x32414658 in all the records where the field1 was 2AFX. What is that mean now? By the way, database is in SQL Server 2005 Enterprise Edition. Thanks mamun Tom Cooper wrote: My guess is that there are some other unprintable characters in that column. Try the following - replace ### with however long your field1 column is, that is, if field1 is varchar(25), use varbinary(25). That should let you see in hex exactly what is in that column. Select Cast(field1 As varbinary(###)), * from table Tom Show quote > > "mamun" <mamun***@hotmail.com> wrote in message > news:1156524010.461385.180440@74g2000cwt.googlegroups.com... > > Hi All, > > > > I have a field in a table which is varchar data type. > > > > Some records in that field of the table starts with number such as > > 2AFX. > > > > When I try to retreive records for that field I do not get any records > > ( select * from table where field1='2AFX'). But I see records are > > there. > > > > I feel this will be a very simple fix and that is the reason I am not > > posting DDLs. > > > > I do highly appreciate your help as this is killing my time. > > > > Thanks a million in advance. > > > > best regards, > > Mamun > > That should be a '2AFX' with no additional hidden characters. I'm not using
SQL 2005 so I can't test on that, but see no reason while the version or edition of SQL server you are running on should cause this - but I could certainly be wrong about that. As Jim suggested, it might be some problem with collations or something like that. If you can give us a script that has DDL and Insert statements that reproduce this problem, we could probably help you faster. If you can't give us something that duplicates the problem, check the collation of the column (you can do that with sp_help <tablename>, and the default collation of your server. You can do that with Select ServerProperty('Collation') But the truth is, I'm not at all sure what is causing this. Maybe someone else on the list will be able to give you more help. Tom Show quote "mamun" <mamun***@hotmail.com> wrote in message news:1156527118.325964.32120@i42g2000cwa.googlegroups.com... > Hi, > > When I tried using your code, I got the following. > > 0x32414658 in all the records where the field1 was 2AFX. > What is that mean now? By the way, database is in SQL Server 2005 > Enterprise Edition. > > Thanks > mamun > > Tom Cooper wrote: > My guess is that there are some other unprintable characters in that > column. > Try the following - replace ### with however long your field1 column > is, > that is, if field1 is varchar(25), use varbinary(25). That should let > you > see in hex exactly what is in that column. > > Select Cast(field1 As varbinary(###)), * from table > > Tom >> >> "mamun" <mamun***@hotmail.com> wrote in message >> news:1156524010.461385.180440@74g2000cwt.googlegroups.com... >> > Hi All, >> > >> > I have a field in a table which is varchar data type. >> > >> > Some records in that field of the table starts with number such as >> > 2AFX. >> > >> > When I try to retreive records for that field I do not get any records >> > ( select * from table where field1='2AFX'). But I see records are >> > there. >> > >> > I feel this will be a very simple fix and that is the reason I am not >> > posting DDLs. >> > >> > I do highly appreciate your help as this is killing my time. >> > >> > Thanks a million in advance. >> > >> > best regards, >> > Mamun >> > > Mamun,
Is this your complete query? select * from table where field1='2AFX' I doubt it, since "table" is not a valid table name. My guess is that your complete query is different, perhaps something like select * from table where field1='2AFX' and field2=1 or field2=3 and it's something else in the query that you are getting wrong. There are other possibilities - perhaps you are selecting from a view and hitting a bug that was fixed many service packs ago. There are a lot of reasons that could explain this, and the reason we ask you to post the DDL is because the problem is often not where you think it is. Steve Kass Drew University www.stevekass.com mamun wrote: Show quote >Hi All, > >I have a field in a table which is varchar data type. > >Some records in that field of the table starts with number such as >2AFX. > >When I try to retreive records for that field I do not get any records >( select * from table where field1='2AFX'). But I see records are >there. > >I feel this will be a very simple fix and that is the reason I am not >posting DDLs. > >I do highly appreciate your help as this is killing my time. > >Thanks a million in advance. > >best regards, >Mamun > > >  |
|||||||||||||||||||||||
Other interesting topics