SQL -- How do I remove trailing spaces


G

Guest

I have created a simple sql statement that displays a couple of columns from
the first table based one column being equal in both linked excel tables and
another column not equal. It is the not equal that does not seem to work and
it looks like there is trailing space in this column but only in some of the
records.

How can I with the SQL statement remove (trim) the trailing space?

SELECT Weekly_Lab_Data_inbound.NG_Identifier,
Weekly_Lab_Data_inbound.ApplicationName
FROM Weekly_Lab_Data_inbound INNER JOIN weekly_lab_data_outbound ON
Weekly_Lab_Data_inbound.NG_Identifier = weekly_lab_data_outbound.NG_Identifier
where Weekly_Lab_Data_inbound.NG_Identifier =
weekly_lab_data_outbound.NG_Identifier
and Weekly_Lab_Data_inbound.ApplicationName <>
weekly_lab_data_outbound.ApplicationName;
 
Ad

Advertisements

D

Douglas J. Steele

You actually had the answer in your question: you use the Trim function!

SELECT Weekly_Lab_Data_inbound.NG_Identifier,
Weekly_Lab_Data_inbound.ApplicationName
FROM Weekly_Lab_Data_inbound INNER JOIN weekly_lab_data_outbound ON
Weekly_Lab_Data_inbound.NG_Identifier =
weekly_lab_data_outbound.NG_Identifier
where Trim(Weekly_Lab_Data_inbound.NG_Identifier) =
Trim(weekly_lab_data_outbound.NG_Identifier)
and Trim(Weekly_Lab_Data_inbound.ApplicationName) <>
Trim(weekly_lab_data_outbound.ApplicationName)
 
G

Guest

Hi Doug,



Douglas J. Steele said:
You actually had the answer in your question: you use the Trim function!

SELECT Weekly_Lab_Data_inbound.NG_Identifier,
Weekly_Lab_Data_inbound.ApplicationName
FROM Weekly_Lab_Data_inbound INNER JOIN weekly_lab_data_outbound ON
Weekly_Lab_Data_inbound.NG_Identifier =
weekly_lab_data_outbound.NG_Identifier
where Trim(Weekly_Lab_Data_inbound.NG_Identifier) =
Trim(weekly_lab_data_outbound.NG_Identifier)
and Trim(Weekly_Lab_Data_inbound.ApplicationName) <>
Trim(weekly_lab_data_outbound.ApplicationName)
 
G

Guest

Hi Doug,
Thanks for the quick reply, I did try trim and rtrim but they do
not seem to work. Is it because they are linked tables? or have you any other
ideas?

Thanks
 
G

Gary Walter

Hi Givnik,

PMFBI

I don't understand why you repeat
your ON clause in WHERE clause...

(if they needed trimmed in WHERE,
then they needed trimmed in ON)

SELECT
I.NG_Identifier,
I.ApplicationName
FROM
Weekly_Lab_Data_inbound As I
INNER JOIN
weekly_lab_data_outbound As O
ON
Trim(.[NG_Identifier]) = Trim([O].[NG_Identifier])
WHERE
Trim(.[ApplicationName]) <> Trim([O].[ApplicationName]);


good luck,

gary
 
D

Douglas J. Steele

Good catch, Gary. I never even looked at the ON clause.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Gary Walter said:
Hi Givnik,

PMFBI

I don't understand why you repeat
your ON clause in WHERE clause...

(if they needed trimmed in WHERE,
then they needed trimmed in ON)

SELECT
I.NG_Identifier,
I.ApplicationName
FROM
Weekly_Lab_Data_inbound As I
INNER JOIN
weekly_lab_data_outbound As O
ON
Trim(.[NG_Identifier]) = Trim([O].[NG_Identifier])
WHERE
Trim(.[ApplicationName]) <> Trim([O].[ApplicationName]);


good luck,

gary

Givnik said:
Hi Doug,
Thanks for the quick reply, I did try trim and rtrim but they do
not seem to work. Is it because they are linked tables? or have you any
other
ideas?

Thanks
 
Ad

Advertisements

G

Guest

Hi Gary,
Many thanks for your time and assitance, sorry about the late reply
but just got back, I tried your suggestion but it did not work. I do have a
work around which seems to work...

Because the outbound table has the extra space I just use left(outbound
column ,len(inbound column)) this does work.

I wonder if certian functions do not work on linked (excel) tables.... just
a guess.

SELECT
I.NG_Identifier,
I.ApplicationName,
O.NG_Identifier,
O.ApplicationName
FROM
Weekly_Lab_Data_inbound As I, weekly_lab_data_outbound As O
where I.NG_Identifier = O.NG_Identifier
and I.ApplicationName <> left(O.ApplicationName, len(I.ApplicationName) );


Thanks for both of your inputs....

Givnik

Gary Walter said:
Hi Givnik,

PMFBI

I don't understand why you repeat
your ON clause in WHERE clause...

(if they needed trimmed in WHERE,
then they needed trimmed in ON)

SELECT
I.NG_Identifier,
I.ApplicationName
FROM
Weekly_Lab_Data_inbound As I
INNER JOIN
weekly_lab_data_outbound As O
ON
Trim(.[NG_Identifier]) = Trim([O].[NG_Identifier])
WHERE
Trim(.[ApplicationName]) <> Trim([O].[ApplicationName]);


good luck,

gary

Givnik said:
Hi Doug,
Thanks for the quick reply, I did try trim and rtrim but they do
not seem to work. Is it because they are linked tables? or have you any
other
ideas?

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top