1

I am working with a firebird database and my problem is that all my data is stored with whitespace on the 'right'. When I query some information to use it in a different program (AutoIt) I am stuck with the whitespace.

UPDATE tableName SET columnName = TRIM( columnName )
UPDATE DETECTION SET DNAME =  right(TRIM  (DNAME));

I have tried such things but it is useless, unfortunately.

I am trying to update my whole database because all the trim right is useless for me!

----------------------------UPDATE--------
I am still having problems

It does not work, maybe I am just stupid with also executing it proper

SELECT a.DNAME, a.DTYPE, a.WINNAME, a.AREA, a.COLOR, a.MOUSE
FROM DETECTION a -- this is my table

when i try;

UPDATE DETECTION SET DNAME = trim( DNAME)

Executing...
Done.
287 fetches, 56 marks, 0 reads, 0 writes.
0 inserts, 28 updates, 0 deletes, 0 index, 28 seq.
Delta memory: 8648 bytes.
DETECTION: 28 updates. 
28 rows affected directly.
Total execution time: 0.021s
Script execution finished.
Commiting transaction...
Done.
Starting transaction...
Preparing query: UPDATE DETECTION SET DNAME = trim( DNAME)
Prepare time: 0.022s
PLAN (DETECTION NATURAL)

when I try

UPDATE DETECTION SET DNAME = trim(tailing from DNAME)

i get the following error

Error: *** IBPP::SQLException ***
Context: Statement::Prepare( UPDATE DETECTION SET DNAME = trim(tailing from DNAME)  )
Message: isc_dsql_prepare failed

SQL Message : -206
Column does not belong to referenced table

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -206
Column unknown
TAILING
At line 1, column 43

even when the first one executes proper it does nothing, my queries are still for with white-space trail at the end.

Maveron
  • 71
  • 1
  • 8
  • are you sure that what you have is spaces and not tabs? if so trim should do the trick – Saikios Mar 22 '16 at 19:24
  • 2
    What is the datatype of those columns? if it is CHAR, than that whitespace is expected, and if you don't want it, you should use VARCHAR instead. BTW the keyword in TRIM is tRailing (with an R). – Mark Rotteveel Mar 22 '16 at 22:11

3 Answers3

0

You shoud use rtrim 

UPDATE tableName SET columnName = rtrim( columnName )
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Firebird doesn't have a function called `rtrim` (at least not built-in). – Mark Rotteveel Mar 24 '16 at 09:26
  • @MarkRotteveel i have found this http://www.firebirdsql.org/refdocs/langrefupd25-udf-rtrim.html – ScaisEdge Mar 24 '16 at 11:04
  • 1
    Yes, but it is not a built-in function, it is a UDF and you need to explicitly declare it to be able to use it (contrary to [`trim`](http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-functions-scalarfuncs.html#fblangref25-functions-scalarfuncs-trim) which is built-in). – Mark Rotteveel Mar 24 '16 at 13:44
0

You can use trailing:

UPDATE tableName SET columnName = TRIM(trailing from  columnName )

Default is BOTH

Trim firebird documentation

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Peter
  • 27,590
  • 8
  • 64
  • 84
0

First datatype should be varchar. Secondly you should check if you still have trailing white spaces. Usually trim removes it ok. Pick-up a field you know the content. Lets say Mary, ID = 100. Do the following: select char_lenght(yourfield) from your table where id = 100 Compare with: select char_lenght(trim(yourfield)) from your table where id = 100. I trust you do not have white spaces in fact.

Hyperbyte
  • 1
  • 1