SQL : to delete spaces in a string

as400

    Sponsored Links

    Next

  • 1. List of file members from VB or Java app?
    I am trying to do something which I thought would be extremely easy, but I can't find an obvious way to do it. I just want to get a list of file members from an arbitrary file on an AS400 from a client application. I looked at the Java class com.ibm.as400.access.AS400File in JTOpen, but can't see how I can use it for this purpose. The getMemberName method looks like it only returns the name of the default member. Also, I found the call cwbDB_RetrieveMemberInformation in the "Optimized SQL APIs" (for C/C++), but there is a warning that this API is deprecated. Regardless, I can't find any explicit instructions on using this from VB; I can't even add a reference to the cwbdb.dll (VB gives an error). So am I missing something really obvious? What is the easiest/best way to do this? I really don't want to make a call to DSPFD and sift through the returned text... Thanks.
  • 2. WDSC Client Install ? and Vista
    I have the CD's for WIN2000 and XP, can I use these on Vista?? Thanks.
  • 3. job submit and library list
    Hi. I'm trying to run a job from job scheduler on V5R4. It's a simple CALL. I'm running it from a user with specific job description, I also use that jobd as a JOBD parameter. I need this job description to configure job library list (program I'm trying to call is in library which isn't in default OS list). I do the same thing on other system with V5R3. On V5R4 job submits without library list set in my specific job description and I get an error that program I'm trying to CALL can't be found in *LIBL. Can anybody tell me what I possibly do wrong ? Tomasz
  • 4. Java read french spool
    Hi, I'm trying to read a spool from an iSeries with a java program. Everything is ok except that i cannot read french special characters like "???. They are always replaced by junk characters. Does anyone have encountered a such problem ? Any clue would be appreciated. Thanks. My code : AS400 ancetre = new AS400("172.16.0.30", "user", "pass"); SpooledFile splf = new SpooledFile(ancetre, "ETAT", 1, "QPADEV0005", "FAB", "626188"); PrintParameterList pl = new PrintParameterList(); pl.setParameter(SpooledFile.ATTR_MFGTYPE, "*WSCST"); pl.setParameter(SpooledFile.ATTR_WORKSTATION_CUST_OBJECT, "/QSYS.LIB/QWPDEFAULT.WSCST"); InputStream in = splf.getTransformedInputStream(pl); byte[] buf = new byte[32767]; StringBuffer sbuf = new StringBuffer(); int bytesRead = 0; do { bytesRead = in.read( buf ); if (bytesRead > 0) { sbuf.append(new String(buf,1,bytesRead)); } } while ( bytesRead != -1 ); System.out.println(sbuf.toString());
  • 5. Embedded SQL in RPG with *ISO dates
    I am new to SQL and have been writting some programs to get a taste of SQL. I seem to be running into a wall everytime I mess with a file that has *ISO dates (2007-12-31) that don't have anything in them besides 0001-01-01. It causes an SQL error every time. Our ERP system has all kinds of date *ISO date fields that are initially set to 0001-01-01 until a real date is needed to be stored. Is there some kind of compile option or something to use to stop this error? There must be something stupid I am doing or SQL is never going to work well with our package. Anybody have any ideas?

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 95 guest