SQL : to delete spaces in a string

as400

    Sponsored Links

    Next

  • 1. Error 214745627 cwbdb00036
    This is not the exact message, but it is very close. The error does end with 00036 and I read about errors like this in the IBM online tech reference, but all it will tell me about error cwbdb00036 is to refer to the "DB2 FOR OS/400 SQL Programming: SC41-3611". I am not an AS400 programmer and I need help. I am an MSOffice consultant and I'm trying (very hard) to do a DSNless connection to a table in an AS400 based database. My connection is good, but when I try my SQL against it, I get this error, which is going to make me crazy. I don't have the manual, I'm not going to buy one for 1 error, and I won't be working with AS400 after this. -- RMC,CPA
  • 2. Need Web Development Info
    We've just taken on a project to develop some web based applications. Can anyone recommend a good book to learn Web based technology, specifically Active Server Pages. We're mostly an AS400 shop (RPG, CL etc.) so something geared toward iSeries people would be ideal. Ami XXXX@XXXXX.COM
  • 3. Sharing a Webfacing project using CVS
    Need a little help. I'm new to using Webshere Development Studio and CVS. I am developing a webfaceing project and want to share it between developers. I believe I have been successful in updating the CVS server with the new project. Here's my problem. When I add the project on the second developer's workspace (going to the CVS repository view and checking out the project) it appears to successfully add the project and I see it in the webfacing perspective. Without making any changes to the project I then select run on server and when the application is run in the browser it starts displaying errors when the application screen is displayed that seem to relate to the command keys jsp. I'm an AS/400 programmer with vary little experience with Java. When I test the project on the original developers machine I get no errors. It appears to me that not everything was saved when I shared the project to CVS from the original developers workspace (I did this from the Web perspective selected the project and then selected Team, Share project. Once this was done I committed the project and it appears that all the information was updated on the repository. I saved both the webface project and the EAR project). Any ideas on what I should look at to solve this problem?????
  • 4. Menu Controll Access Program
    I am looking for a cheap way to control access to menu functions. It needs to be configurable by signon. I appreciate any help Thanks Hoss

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