Deleting Spaces!

I am tring to join two tables. There is one problem of course. There is one column I would be able to join the two tables by. This column would be Loc_Code. The only problem is that both columns are not exactly the same. They look like this:

Table 1 Table 2
Loc_Code Loc_Code
A 12345 A12345
A 12346 A12346
A 12347 A12347
A 12348 A12348

I need to erase the spaces that exists in the Loc_Code column in table 1 so that I can join with table 2.

All help would be appreciated.Try this query on your table:

UPDATE [Table 1] SET Loc_Code = Replace([Loc_Code], (Chr(32)), "");

This should get rid of that space.|||You can also use trim -

select a1.col1, a2.col1
from test a, test1 a2
where a1.col3 = trim(a2.col3)|||You can also use trim -

select a1.col1, a2.col1
from test a, test1 a2
where a1.col3 = trim(a2.col3)
!!Cough!!Bullsht!!Cough!!|||aw, c'mon, nocopy, be nice, show the poor guy the right way

since this is the SQL forum, for the SQL language and not any specific implementation thereof, i shall give an SQL solution

estefex, here's your join --
select Table1.foo
, Table2.bar
from Table1
inner
join Table2
on substring(Table1.Loc_Code from 1 for 1)
|| substring(Table1.Loc_Code from 3 for 5)
= Table2.Loc_Code|||Mmm, sorry r937 I'll tone it down. ;)

dj982020 already gave a solution, the replace thing it is.

By the way, your code won't run on my DB. The replace will though.

ss659 No hard feelings mmmkay? ;)|||dj982020's solution will work only in microsoft databases

and if your database does not run standard sql, i suggest you get a better database

:cool: :cool: :cool:|||r937, your code is not robust also.
The replace will plow through as many spaces as you throw at it.
Well I would use it in a join instead of updatin' cause maube the other table wants it this way.

I feel mighty feisty today.|||dj982020's solution will work only in microsoft databases

and if your database does not run standard sql, i suggest you get a better database

:cool: :cool: :cool:
You being bad too.

trim takes one character only 'tsup with dat?
rtrim ltrim kicks bigger A$$.|||maybe estefex's database does not have the replace or trim functions, did you ever consider that?

do you even know what database estefex is running?

no

therefore standard sql is the best solution

and trim will not remove a space from inside a value|||and trim will not remove a space from inside a value

Cough!!True!!Cough!!

Now I KNOW you know standard SQL better than me.
Is this the best standard SQL can do then? :eek:|||well, i don't really want to get into a discussion of whether standard sql is any good or not, or "the best it can do"

all i wanted to do was point out that in this forum, standard sql should be used

especially if the poster does not indicate which database system they're using

i mean, if somebody wanted an oracle solution, there's a forum for oracle

if somebody wanted an access solution, there's a forum for access

if somebody wanted an sql server solution, there's a forum for sql server

if somebody wanted a mysql solution, there's a forum for mysql

what do you think this forum is for?|||R937
Chill, chill. You right. :cool:

Maybe folks ought to mention what DB or DBs they are running.
Then there would be no confusion.
Who needs to read the crystal reports err.. bowl, right? ;)|||!!Cough!!Bullsht!!Cough!!

Funny - it says you have a WHOPPING 23 posts, 6 of which are on this page. Ive noticed you have not actually given any of your OWN ideas, just sat back and critiqued every one elses :) http://www.dbforums.com/showpost.php?p=3671341&postcount=9 One of his better STELLAR posts again..bringing so much to the table. Im sure you have an important job out in the community though so you're too busy to formulate your own thoughts.

And to be honest I never looked at the data for the original post - I just read " I want to erase spaces in one table to join to another" - Trimming a column will take care of the leading and trailing spaces, and yes you're right won't trim within a column. But as we ALL know, most of the data people post is not what actually resides in the actual database. Im sure he really has a table called table1 and table2...riiight..you idiot!|||Funny - it says you have a WHOPPING 23 posts, 6 of which are on this page. Ive noticed you have not actually given any of your OWN ideas, just sat back and critiqued every one elses :) http://www.dbforums.com/showpost.php?p=3671341&postcount=9 One of his better STELLAR posts again..bringing so much to the table. Im sure you have an important job out in the community though so you're too busy to formulate your own thoughts.

And to be honest I never looked at the data for the original post - I just read " I want to erase spaces in one table to join to another" - Trimming a column will take care of the leading and trailing spaces, and yes you're right won't trim within a column. But as we ALL know, most of the data people post is not what actually resides in the actual database. Im sure he really has a table called table1 and table2...riiight..you idiot!
Hah, if you got a high post count you think you are the shit? :D
Maybe you ought to start reading the question before you respond.
Now, I am not going to plow through the millions of your posts and see if they are of the similar quality as the one here. And unlike you I am not going to call you names.

By the way, did you ask r937 if he was offended by my posts? I think not.
Have a nice life.
Apologies for the Idiot will be accepted. :rolleyes:

deleting hyphens in a phone field

I am trying to delete hyphens and spaces from a field containing phone
numbers. I know that the "Replace" Function should allow me to do this but
I
don't know how. Every phone number is different, so I don't have a 1st valu
e
to put into the arguments. I am very new to SQL Server and this is driving
me nuts.I don't understand what you mean by '1st value'. You can nest REPLACE
functions to remove both spaces and hyphens in one pass:
UPDATE dbo.MyTable
SET Phone = REPLACE(REPLACE(Phone, '-',''), ' ', '')
Hope this helps.
Dan Guzman
SQL Server MVP
"John H." <John H.@.discussions.microsoft.com> wrote in message
news:03E99E25-D30F-4C4E-839D-F25015B38FA2@.microsoft.com...
>I am trying to delete hyphens and spaces from a field containing phone
> numbers. I know that the "Replace" Function should allow me to do this
> but I
> don't know how. Every phone number is different, so I don't have a 1st
> value
> to put into the arguments. I am very new to SQL Server and this is
> driving
> me nuts.|||John H. a écrit :
> I am trying to delete hyphens and spaces from a field containing phone
> numbers. I know that the "Replace" Function should allow me to do this bu
t I
> don't know how. Every phone number is different, so I don't have a 1st va
lue
> to put into the arguments. I am very new to SQL Server and this is drivin
g
> me nuts.
This function :
/ ****************************************
***********************************
*/
-- delete all undesirable chars
/ ****************************************
***********************************
*/
-- exemple : FN_RESTRICT('_ Paris...?', 'abcdefghijklmnopqrstuvwxyz')
=>'aris'
CREATE FUNCTION F_RESTRICT (@.IN VARCHAR (8000),
@.CHARSOK VARCHAR(256))
RETURNS VARCHAR (8000)
AS
BEGIN
-- effets de bord
IF @.IN IS NULL
RETURN NULL
IF @.CHARSOK IS NULL
RETURN NULL
IF LEN(@.IN) = 0
RETURN @.IN
-- initialisation
DECLARE @.I INTEGER
DECLARE @.OUT VARCHAR(8000)
SET @.OUT = ''
-- lecture caractère par caractère
SET @.I =1
WHILE @.I <= LEN(@.IN)
BEGIN
IF PATINDEX('%' + SUBSTRING(@.IN, @.I, 1)+ '%', @.CHARSOK) > 0
SET @.OUT = @.OUT + SUBSTRING(@.IN, @.I, 1)
SET @.I = @.I + 1
END
RETURN @.OUT
END
GO
Use it in a trigger :
UPDATE MyTable
SET PHONE_NUMBER = F_RESTRICT (PHONE_NUMBER, '0123456789')
You will have a column with only figures in it.
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************sql

Delete Trailing ''\''

Hi,

I need to delete trailing slashes ('\') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat\, Rajneesh, Ankush\, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

If you know that you only have 1 trailing slash then you could use:

Code Snippet

SELECT LEFT(NameCol, LEN(NameCol)-1)

or if you know you want to trim everything after/including the slash then:

Code Snippet

SELECT LEFT(NameCol, PATINDEX('%/', NameCol)-1)

HTH!

|||

To achieve this, you can use reverse to reverse the value and check the first value for a \

create table test
(
value varchar(20)
)
insert into test
select 'Rajat\'
union all
select 'Rajneesh'
union all
select 'Ankush\'
union all
select 'Sudheer'
go
select case when left(reverse(value),1) = '\'
then left(value,len(value) - 1) else value end
from test

Returns:

--
Rajat
Rajneesh
Ankush
Sudheer

Delete Trailing ''\''

Hi,

I need to delete trailing slashes ('\') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat\, Rajneesh, Ankush\, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

If you know that you only have 1 trailing slash then you could use:

Code Snippet

SELECT LEFT(NameCol, LEN(NameCol)-1)

or if you know you want to trim everything after/including the slash then:

Code Snippet

SELECT LEFT(NameCol, PATINDEX('%/', NameCol)-1)

HTH!

|||

To achieve this, you can use reverse to reverse the value and check the first value for a \

create table test
(
value varchar(20)
)
insert into test
select 'Rajat\'
union all
select 'Rajneesh'
union all
select 'Ankush\'
union all
select 'Sudheer'
go
select case when left(reverse(value),1) = '\'
then left(value,len(value) - 1) else value end
from test

Returns:

--
Rajat
Rajneesh
Ankush
Sudheer

Delete Trailing ''\''

Hi,

I need to delete trailing slashes ('\') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat\, Rajneesh, Ankush\, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

If you know that you only have 1 trailing slash then you could use:

Code Snippet

SELECT LEFT(NameCol, LEN(NameCol)-1)

or if you know you want to trim everything after/including the slash then:

Code Snippet

SELECT LEFT(NameCol, PATINDEX('%/', NameCol)-1)

HTH!

|||

To achieve this, you can use reverse to reverse the value and check the first value for a \

create table test
(
value varchar(20)
)
insert into test
select 'Rajat\'
union all
select 'Rajneesh'
union all
select 'Ankush\'
union all
select 'Sudheer'
go
select case when left(reverse(value),1) = '\'
then left(value,len(value) - 1) else value end
from test

Returns:

--
Rajat
Rajneesh
Ankush
Sudheer