?
Solved

Remove Spaces from items pulled from SQL Table.

Posted on 2006-05-23
6
Medium Priority
?
508 Views
Last Modified: 2012-05-05
Remove Spaces from items pulled from SQL Table.

I have Model and Serial numbers that some spaces at the begaining and end.  I need to show the vales on screen so we can print them with barcodes.  Beacause of spaces this does not work.

<%
 dim dia, strSQL, rs, strSQL1, rs1, ordno, strSQL2, rs2, strSQL3, rs3, strSQL4, rs4, znToAdd, strSQL5, srl
ordno= Request.QueryString("ordno")
  strSQL = "SELECT * FROM sfordfil_sql where ord_no like '" & ordno & "'"
  set rs = Conn.Execute(strSQL)
  strSQL2 = "SELECT * FROM labels_typ WHERE item_no = '" & rs("item_no") & "'"
  set rs2 = Conn.Execute(strSQL2)
%>
      MODEL ID#: <%=rs2("mdl_detail")%><br>
      <font face="IDAutomationC39XS" size="3">*<%=rs2("mdl_detail")%>*</font><br>


0
Comment
Question by:kwitcom
  • 3
  • 2
6 Comments
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 2000 total points
ID: 16746215
you need to tell use the database system you are using  for us to give the correct syntax...


SQL Server

use LTRIM(RTRIM(your Column))


DB2/UDB

use STRIP(yourCOlumn,B,' ')       (mainframe ver 6&7)
or TRIM(yourColumn,B,' ')


etc...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16746220
many dbms also have a replace function which would allow you to remove all spaces ....

but that would remove internal spaces as well.
0
 

Author Comment

by:kwitcom
ID: 16746316
MS SQL, from a ASP page.  I don't want to change the data in the table.  just the output of it.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 8

Expert Comment

by:infolurk
ID: 16747984
MS SQL doesnt have a trim function, but lowfat's LTRIM(RTRIM(your Column)) should work. If you use this in a select query it will only trim the query output, not the data in the table.
0
 

Author Comment

by:kwitcom
ID: 16750283
Where in my Query line do I put it?

  strSQL2 = "SELECT * FROM labels_typ WHERE item_no = '" & rs("item_no") & "'"

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 16753850
you need to specifically specify the column names you want returned...

e.g.

strSQL2 = "SELECT col1,ltrim(rtrim(serial)) as serial,ltrim(rtrim(model)) as model,col3,col4,... FROM labels_typ WHERE item_no = '" & rs("item_no") & "'"
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question