SQL -- How do I remove trailing spaces

  • Thread starter Thread starter Guest
  • Start date Start date
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;
 
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)
 
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)
Click to expand...
 
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
 
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
 
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
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
 
Back
Top