SQL : to delete spaces in a string

as400

    Sponsored Links

    Next

  • 1. RPG equivalent of this C structure from recio.h???
    Thanks a bunch. I will say this much to help out. 'int's in C are 4 bytes long on the 400. The three ints at the last of this structure are individual bits or a group of 6. C will add up these bit fields and place them into 1 int or 4 bytes. C reports that the size of this structure is 64 bytes on the 400. 16 bytes for the two pointer, 4 bytes each for longs, 2 bytes for short, 1 byte for char, 1 byte for the group of three int bit fields, and 20 for the character array. so, (16 * 2) + 4 + 4 + 2 + 1 + 1 + 20 typedef struct { unsigned char *key; _Sys_Struct_T *sysparm; unsigned long rrn; long num_bytes; short blk_count; char blk_filled_by; int dup_key : 1; int icf_locate: 1; int reserved1 : 6; char reserved2[20]; } _RIOFB_T; --- Kelly Beard, a.k.a. Mr. K.V.B. Liar
  • 2. Urgent : Sockets question
    Hi all, Firstly, I haven't posted on this N.G. for around a year-and-a-half (don't shoot, but I've been doing .NET programming). Finally, I'm back to good old green-screen/VB/asp and I must say that I downloaded both headers and messages and am surprised at such a small number of people using this N.G. Have people moved their questions elsewhere and, if so, where? My problem . . . I have created an RPG 4 sockets progam which sends small amounts of data to a VB 6.0 dll. This part is fine but my RPG program is initiating the connection request and I'm unsure of how to use the same estabilished connection to send a small amount of data back to the RPG program. I'm using the simple winsock control in VB and I nkow hoe to send the data from there but how do I code to receive the data immediately after a send in my RPG program? I really would appreciate prompt help with this as I need to move on to other 'things' on the '400. Regards John.
  • 3. Disk Usage Spiralling Upwards.
    Our AS/400 720 storage has shot up from 63% used to 91% in just 3 weeks. I have monitored file sizes and can't find any significant increase. What else could be eating up disk space and how to I check? Thanks, John.
  • 4. buy model 170
    Hi. I`d like to buy a model 170 in Poland. Does anybody have any information about this? Regards, Tom

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