SQL : to delete spaces in a string

as400

    Sponsored Links

    Next

  • 1. ISeries Access linux wrong characters displayed
    Hello, I have a problem with iSeries Access linux version 1.8 (latest one to date). The problem is that I have some characters which are not displayed correctly e.g. @ become ? ?become { and so on. When I use a windows emulation everything is fine with the characters, what can be wrong ? Btw, in the iSeries linux help (which is in french), there is no accentuated characters. A few configuration variable : on the linux box : LANG=fr_FR@euro on the iSeries : QCCSID = 37; QCHRID=637,37; QLANGID=ENU; QKBDTYPE=CAI Thanks for your help. O.D.
  • 2. iSeries (AS400) IQ, and Exchange
    I have a product on my iSeries box, IQ (Interactive Query) which can create PDF files of reports, and supposed to have the ability to e-mail them by relaying through our mail server, which is exchange. I can send those PDF files to any outside e-mail addresses (hotmail, gmail, etc) however if I send that same file to my internal address on our domain, the e-mail seems to just simply not get sent. The IQ support line says that since we can relay to a hotmail or gmail, the iSeries box is set up right. Question is, why is Exchange not sending the messages internally? Thx!
  • 3. AS/400 Administration Training/Certification
    All: I'm interested in pursuing a career focusing around administrating IBM I-Series AS/400 systems. To that end I am currently researching training and certification programs that are known to be good, credible and cost-effective. These may be online, or otherwise. If anyone has any recommendations, I would appreciate the information. Thanks in advance. John
  • 4. IFS premissions and object ownership problems
    Hi, I have just taken over an intranet which is sitting on the IFS of an AS400 running 5.2. I have converted all server instances to Apache from the origional AS400 HTTP servers. The website is 4Gb in size. There are lots of different owners of the objects and the premissions are a mess. Has anybody got any advice on how to sort out the mess. All is working now but I would like to put some structure to the whole thing and it is a security nightmare as it is I dont know who has premission to what. Thanks Dom

SQL : to delete spaces in a string

Postby Jean-Claude MONOT » Sun, 22 Feb 2004 03:59:34 GMT

Hello

using sql, how can I delete all the spaces in a string ?
Example : I have "I WANT TO READ" and I need to obtain "IWANTTOREAD".
Is there a way to do it ?

Thanks.


 XXXX@XXXXX.COM 
www.asimov.fr.st - Isaac Asimov, un monde de science-fiction
- - - " La {*filter*} est le dernier refuge de l'incompence " - - -



Re: SQL : to delete spaces in a string

Postby Jonathan Ball » Sun, 22 Feb 2004 05:16:21 GMT




As far as I can tell, there is no SQL standard scalar 
function that will do what you want to do.  However, 
you could write your own user function, either entirely 
in SQL or in a high-level language like RPG or COBOL, 
that you could then use to do the conversion in a 
subsequent SQL statement.  I think it probably would be 
easier to write it in an HLL, but you could do it in a 
pure SQL function.

Assuming your function is called something like 
RmvSpaces, you then would code

    select col1, col2,...RmvSpaces(expr)
      from ...

where 'expr' is either some column containing the 
string value from which you want to remove spaces, or 
some other expression derived from other columns.

AVERTIS{*filter*}T!  A mon avis, cette fonction risque de ne 
pas re tr performant, surtout la premie fois que 
tu l'invoques.

Bonne chance!


Re: SQL : to delete spaces in a string

Postby Drew Dekreon » Wed, 25 Feb 2004 09:24:59 GMT

Not in SQL.
I'd create a CL or RPG program and use a CREATE PROCEDURE to make in 
available in sql




Similar Threads:

1.empty string versus space within a definition of a materialized query table

I'm wondering, why I can't re-use the same SELECT definitition to
create a MQT if the SELECT statement has an empty string instead a space
as the default value for a column (DB2/6000 8.2.9 AIX
5.3).

Both the sample script and the output are attached.

#!/bin/sh
#

db2 -v connect to TLMA 1>/dev/null

db2 -v "create table EXT.TORALF (ID int)"

TORALF_SPACE="select ID, ' ' as ALIAS from EXT.TORALF"
TORALF_EMPTY="select ID, ''  as ALIAS from EXT.TORALF"

for i in TORALF_SPACE TORALF_EMPTY
do
	echo
	echo "$i-------------------------------------------------------------------------------"
	
	echo	
	eval $(echo db2 -v "export to ./tmp of del modified by nochardel coldelx09 \$$i")

	echo
	db2 -v "drop table EXT.$i"
	
	echo
	eval $(echo db2 -v "create table EXT.$i as \( \$$i \) data initially deferred refresh deferred")
done

db2 -v "drop table EXT.TORALF"

db2 -v connect reset 1>/dev/null

exit 0



[tfoerste@detlmtst01] /home/tfoerste> ./quote.sh
create table EXT.TORALF (ID int)
DB20000I  The SQL command completed successfully.


TORALF_SPACE-------------------------------------------------------------------------------

export to ./tmp of del modified by nochardel coldelx09 select ID, ' ' as ALIAS from EXT.TORALF
SQL3104N  The Export utility is beginning to export data to file "./tmp".

SQL3105N  The Export utility has finished exporting "0" rows.


Number of rows exported: 0



drop table EXT.TORALF_SPACE
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "EXT.TORALF_SPACE" is an undefined name.  SQLSTATE=42704


create table EXT.TORALF_SPACE as ( select ID, ' ' as ALIAS from EXT.TORALF ) data initially deferred refresh deferred
DB20000I  The SQL command completed successfully.


TORALF_EMPTY-------------------------------------------------------------------------------

export to ./tmp of del modified by nochardel coldelx09 select ID, '' as ALIAS from EXT.TORALF
SQL3104N  The Export utility is beginning to export data to file "./tmp".

SQL3105N  The Export utility has finished exporting "0" rows.


Number of rows exported: 0



drop table EXT.TORALF_EMPTY
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "EXT.TORALF_EMPTY" is an undefined name.  SQLSTATE=42704


create table EXT.TORALF_EMPTY as ( select ID, '' as ALIAS from EXT.TORALF ) data initially deferred refresh deferred
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0604N  The length, precision, or scale attribute for column, distinct type,
structured type, attribute of structured type, function, or type mapping
"ALIAS" is not valid.  SQLSTATE=42611

drop table EXT.TORALF
DB20000I  The SQL command completed successfully.

-- 
MfG/Sincerely

Toralf Fster
pgp finger print: 7B1A 07F4 EC82 0F90 D4C2 8936 872A E508 7DB6 9DA3


2.How to select the data which have space end of string one or more

Env: DB2 V8 + FP13

Question: How to select the data which have space end of string one or
more, like 'AB ','AB   '

Case:
.Create Test Table
db2 "create table test (vname varchar(100)) "

.Insert Test Data
db2 "insert into test values('AB'), ('AB '),('AB  '),('AB   ')"

.Select only have one space end of string 'AB' with follow sql, but
result not match our expectation
db2 "select * from test where vname='AB '"
VNAME
-----
AB
AB
AB
AB

now, our solution use follow sql
db2 "select * from test where length(vname)=3 and VNAME='AB '"

But, Anyone know another alternate way can select string like 'AB ' ?
Thanks

3.Empty ResultSet from executeQuery(String sql) using JTOpen

I have a java.sql.Statement object that I'm using the
executeQuery(String sql) method on to retrieve a ResultSet from a SQL
query. The ResultSetMetaData of the returned ResultSet is proper, and
contains all of the columns I requested in the SQL query, but the
ResultSet itself has no rows in it. I can not figure out why, because
it's a simple SELECT * statement which works when I run it in iSeries
Navigator.

I think the problem may be in how I'm generating the Statement object,
but I'm not sure. I have a JNDI data source defined in Tomcat to use
the
com.ibm.as400.access.com.ibm.as400.access.AS400JDBCConnectionPoolDataSource
bean which I then use the getConnection() method on to get a
java.sql.Connection and then I use the getStatement() method to get a
java.sql.Statement object.

Does anyone think this is out of the ordinary, and if so, what I might
be able to do to fix it? Thanks!

--Ioeth

4.String validation by SQL...

In SQL, I need to validate a string to be sure there is not letter in
the string.  I want only numbers between 0 and 9.

Ex:

'A123456789' is wrong
'0123456789' is valid

Is there any easy way to validate this validity ?
One way we found is to validate whether each caracter is between 0 and
9 with a substring, but it a bit compilcated for nothing.

Thanks, have a great day !

5.SQL/table optimization for string search?

Hello group,

I'm having a difficult time with an SQLRPGLE program.  My problem is
that I have a table with one field only, containing a large string of
char data.  I need to return rows which contain both of 2 host
variables being searched for.  I can't see any way to index this table
that would help as I am using the LIKE predicate as follows:

eval string1# = '%' + %trim(string1) + ' %';
eval string2# = '%' + %trim(string2) + ' %';

exec sql
select field
from table
where field like :string1# and field like :string2# ;

Similar to searching a table for book titles containing 2 search
strings.

Is there anything that might help?  Thanks.

6. Help needed : How to Execute a string SQL statement with DB2

7. SQL-Statment that add to strings

8. Create concatenated string from a result set using SQL



Return to as400

 

Who is online

Users browsing this forum: No registered users and 74 guest