Too Many Spaces Between Words - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Too Many Spaces Between Words

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-10-07 : 15:00:17

I have a field in my database that is the combination of 3 fields from MS Access. The field SubName is made up of First Name, Middle Initial, and Last Name. My problem is that if the person does not have a middle initial listed, the result in the database is the first name followed by 3 spaces, then the last name. Is there anyway to count the number of spaces between words and make sure it is only one? Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-07 : 15:21:59

do you actually care how many spaces there are... or do you just want to force there to be just one?

if its the latter... a series of 3 replaces will do the trick...


I usually use (in SQL)

Replace(Replace(Replace(myCol,' ',' þ'),'þ ',''),'þ','')

so in access it should be

Replace(Replace(Replace(myCol," "," þ"),"þ ",""),"þ","")

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-10-07 : 15:32:24

Do you already have this data in a single column in your table? It would be nice if you had it still in 3 columns; then you could use CASE to return the full name (First MI Last) formatted correctly based on whether MI contains a value...

e.g.

SELECT
		full_name	=
		first_name
	+	CASE
			WHEN	middle_initial	IS NULL	THEN	''
			ELSE	' ' + middle_initial
		END
	+	' ' + last_name
FROM
		name_table

Regards,

Daniel

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-10-07 : 15:44:52

it actually comes out of access as one field but is made up of 3 fields in access. i'll give that code a try. thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-07 : 15:45:10

Maybe something like


DECLARE @x varchar(8000)
SELECT @x = 'Brett  J   Kaiser'
SELECT REPLACE(REPLACE(@x,'  ',' '),'  ',' ')

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-07 : 15:46:38

This data should be stored in 3 columns, then it is the job of the presentation layer on how to format the data. Why were the columns combined when you moved from Access to SQL Server?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-07 : 16:21:04

quote:
Originally posted by tduggan

This data should be stored in 3 columns, then it is the job of the presentation layer on how to format the data. Why were the columns combined when you moved from Access to SQL Server?

Tara

Good Point

Still if you really want FullName, you should fix the Access code using IIF(ISNULL(.....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-10-07 : 17:00:11

Right now the fields come out of Access merged although they started as 3 fields in access. The way I think I'll do it is to have 3 fields come out of access into SQL server. I used the following code to merge them

SELECT
full_name =
first_name
+ CASE
WHEN middle_initial IS NULL THEN ''
ELSE ' ' + middle_initial
END
+ ' ' + last_name
FROM
name_table

And it worked well. There is no extra space when the middle initial is null. The reason they were merged is because they are used for a login system where you typed your first and last name plus a middle initial if you had it. the extra space caused the login to reject correct usernames due to the space if there was no MI.

Thanks for the help.

mriverol
Starting Member

10 Posts

Posted - 2005-10-08 : 04:06:11

Alternately you could add spaces where you would expect them to appear if all fields are populated and then replace any double spaces with a single space

SELECT FULLNAME =
REPLACE(
first_name
+ ' ' + ISNULL(middle_initial, ' ')
+ ' ' + last_name,
' ', ' '
)

Martin

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-10-08 : 04:53:34

quote:
Originally posted by Seventhnight

Replace(Replace(Replace(myCol,' ',' þ'),'þ ',''),'þ','')

If you were in Iceland, this code might be a thorn in your side.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-08 : 23:04:44

... I merely gave a suggestion of a relatively unused character... its the principle that counts

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-10-09 : 08:03:19

http://en.wikipedia.org/wiki/%C3%9E

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 01:57:43

quote:
Originally posted by X002548

Maybe something like

DECLARE @x varchar(8000)
SELECT @x = 'Brett J Kaiser'
SELECT REPLACE(REPLACE(@x,' ',' '),' ',' ')

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Corey's is more efficient?


DECLARE @x varchar(8000)
SELECT @x = 'Brett  J              Kaiser'
select Replace(Replace(Replace(@x,' ',' þ'),'þ ',''),'þ','')
SELECT REPLACE(REPLACE(@x,'  ',' '),'  ',' ')

Madhivanan

Failing to plan is Planning to fail

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-10-16 : 11:21:38

Because of another thread (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56429) I've been experimenting a bit with collations and so on.
Corey's assertion that þ is a good choice because it's little used (unless you're in Iceland) might be true, were it not for the fact that Latin1_General_CI_AI collates 'þ' and 'th' equally.


SELECT Replace(Replace(Replace(myCol,' ',' þ'),'þ ',''),'þ','')
FROM (SELECT 'Do the math Corey, this is a bad thing!'
  COLLATE Latin1_General_CI_AI AS myCol) AS A

returns "Do e ma Corey, is is a bad ing!"

So, use something that doesn't accidentally collate with characters you want to keep! I like '¬' because it's on my keyboard and nobody uses it... or do they?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-16 : 11:52:31

If you are trying to parse name strings, I have posted a UDF in the code forum that should help you out:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56499

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-16 : 20:38:25

quote:
Originally posted by Arnold Fribble

Because of another thread (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56429) I've been experimenting a bit with collations and so on.
Corey's assertion that þ is a good choice because it's little used (unless you're in Iceland) might be true, were it not for the fact that Latin1_General_CI_AI collates 'þ' and 'th' equally.

SELECT Replace(Replace(Replace(myCol,' ',' þ'),'þ ',''),'þ','')
FROM (SELECT 'Do the math Corey, this is a bad thing!'
COLLATE Latin1_General_CI_AI AS myCol) AS A

returns "Do e ma Corey, is is a bad ing!"

So, use something that doesn't accidentally collate with characters you want to keep! I like '¬' because it's on my keyboard and nobody uses it... or do they?

Don't miss quote me... I did not assert that þ is a good choice... only that it is what I usually use. I leave it up to others to determine a safe character(s) for replacements...

for example, you could use a character set to do the replace, as long as you have the room


Declare @ph varchar(10)
Set @ph = 'þ'

SELECT Replace(Replace(Replace(myCol,' ',' '+@ph),@ph+' ',''),@ph,'')
FROM (SELECT 'Do the math Corey, this is a bad thing!'
  COLLATE Latin1_General_CI_AI AS myCol) AS A

Set @ph = '!~!'

SELECT Replace(Replace(Replace(myCol,' ',' '+@ph),@ph+' ',''),@ph,'')
FROM (SELECT 'Do the math Corey, this is a bad thing!'
  COLLATE Latin1_General_CI_AI AS myCol) AS A

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-10-17 : 08:27:05

I'm confused, did you know that using 'þ' would remove all the 'th' before you posted your original reply?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-17 : 08:46:59

No... i did not... and it doesn't on my server's default collation, unless i force it:


Declare @ph varchar(10)
Set @ph = 'þ'

--Defaulting (works)
SELECT Replace(Replace(Replace(myCol,' ',' '+@ph),@ph+' ',''),@ph,'')
FROM (SELECT 'Do the math Corey, this is a bad thing!' 
	AS myCol) AS A

--Forced to server default (does not work)
SELECT Replace(Replace(Replace(myCol,' ',' '+@ph),@ph+' ',''),@ph,'')
FROM (SELECT 'Do the math Corey, this is a bad thing!' 
	COLLATE Latin1_General_CI_AS AS myCol) AS A

--Forced to server default (does not work)
SELECT Replace(Replace(Replace(myCol,' ',' '+@ph),@ph+' ',''),@ph,'')
FROM (SELECT 'Do the math Corey, this is a bad thing!'
  COLLATE Latin1_General_CI_AI AS myCol) AS A

The first one works fine for me

here is a good alternate to 'þ':


Declare @ph varchar(10)
Set @ph = char(10) + char(13)

SELECT Replace(Replace(Replace(myCol,' ',' '+@ph),@ph+' ',''),@ph,'')
FROM (SELECT 'Do the math Corey, 
this is a bad thing!' 
	AS myCol) AS A

SELECT Replace(Replace(Replace(myCol,' ',' '+@ph),@ph+' ',''),@ph,'')
FROM (SELECT 'Do the math Corey, 
this is a bad thing!' 
	COLLATE Latin1_General_CI_AS AS myCol) AS A

SELECT Replace(Replace(Replace(myCol,' ',' '+@ph),@ph+' ',''),@ph,'')
FROM (SELECT 'Do the math Corey, 
this is a bad thing!'
  COLLATE Latin1_General_CI_AI AS myCol) AS A

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-10-17 : 08:59:15

Presumably you're using a SQL_Latin1_General_CP1_... collation? In that case, comparisons, REPLACE, etc. on char and varchar will, as you say, treat 'þ' and 'th' differently. This won't, however, be the case with nchar, nvarchar, which will still use the Windows collation functions. So


SELECT REPLACE('this' COLLATE SQL_Latin1_General_CP1_CI_AI,'þ',''),
 REPLACE('this' COLLATE Latin1_General_CI_AI,'þ',''),
 REPLACE(N'this' COLLATE SQL_Latin1_General_CP1_CI_AI,'þ',''),
 REPLACE(N'this' COLLATE Latin1_General_CI_AI,'þ','')

will return this is is is

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-17 : 10:01:21

K... I'll buy that

would there be any obvious conflicts with lfCr as a placeholder?
or maybe #9786;?

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

SQLServerSpotlight
Yak Posting Veteran

57 Posts

Posted - 2006-01-31 : 19:17:21

How about a more general solution:


create table t (s sysname)
insert into t select 'this     is a                         message.               '

	-- convert tabs to spaces
	update	t set s = replace(s, '	',' ')
	where	charindex('	', s) > 0

	-- now do the work.
	while 1=1
	begin
		update t
		set		s = substring(s, 1, charindex('  ', s, 1)-1) + ' ' + ltrim(substring(s,charindex('  ', s, 1), 8000))
		where	charindex('  ', s, 1) > 0

		if @@rowcount = 0
			break
	end

	select	s
	from	t

Will replace ANY number of duplicate spaces with single spaces for all rows.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources