Retrieval of records

• Murali-220004

  SSC Journeyman

  Points: 81

  More actions

  May 3, 2006 at 2:20 am

  #117420

  Hi,

   

  I have problem in retrieving the records from the table as explain below:

   

  Table Name: table1 (which consists of 2 columns)

   

  Fields : Field1 varchar(10)

               Field2 varchar(40)

   

  Values in the table:

  Field1	Field2
  Test	Text value with no blank space
  Test	Text value with blank space

   

   

  I am trying to retrieve the record, which is having blank space i.e.,

   

  SELECT * FROM table1 WHERE Field1 = ‘Test     ‘

   

  The above query results all the two records instead of retrieving only the second record.

   

  I am waiting for your suggestion on this problem.

   

  Thanks,

  Murali S

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  May 3, 2006 at 3:31 am

  #635518

  select * from table1 where field1 like N'% %'

  you could also do something like

  select * from mytable where field1ltrim(rtrim(field1)

  but this would be very bad against large tables

  also

  select * from mytable where field1 like '[^a-z]'

  MVDBA

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  May 3, 2006 at 4:27 am

  #635530

  SELECT * FROM table1 WHERE Field1 LIKE 'Test     '

  or

  SELECT * FROM table1 WHERE Field1 = 'Test %'

  if unknown number of trailing spaces

  Far away is close at hand in the images of elsewhere.
  Anon.

• Murali-220004

  SSC Journeyman

  Points: 81

  More actions

  May 3, 2006 at 5:10 am

  #635539

  Thanks Mike,

  Each record will be unique and based on the selection i have to update the other field also. This may workout while generating the reports.

  Murali S