SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Too Many Spaces Between Words
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

oahu9872
Posting Yak Master

USA
112 Posts

Posted - 10/07/2005 :  15:00:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 10/07/2005 :  15:21:59  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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 ..."
Go to Top of Page

CorpDirect
Yak Posting Veteran

USA
92 Posts

Posted - 10/07/2005 :  15:32:24  Show Profile  Reply with Quote
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

Edited by - CorpDirect on 10/07/2005 15:34:29
Go to Top of Page

oahu9872
Posting Yak Master

USA
112 Posts

Posted - 10/07/2005 :  15:44:52  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/07/2005 :  15:45:10  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 10/07/2005 :  15:46:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/07/2005 :  16:21:04  Show Profile  Reply with Quote
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
Go to Top of Page

oahu9872
Posting Yak Master

USA
112 Posts

Posted - 10/07/2005 :  17:00:11  Show Profile  Reply with Quote
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.
Go to Top of Page

mriverol
Starting Member

10 Posts

Posted - 10/08/2005 :  04:06:11  Show Profile  Reply with Quote
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
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 10/08/2005 :  04:53:34  Show Profile  Reply with Quote
quote:
Originally posted by Seventhnight

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



If you were in Iceland, this code might be a thorn in your side.
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 10/08/2005 :  23:04:44  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
... 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 ..."
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 10/09/2005 :  08:03:19  Show Profile  Reply with Quote
http://en.wikipedia.org/wiki/%C3%9E
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 10/10/2005 :  01:57:43  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 10/16/2005 :  11:21:38  Show Profile  Reply with Quote
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?
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 10/16/2005 :  11:52:31  Show Profile  Reply with Quote
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
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 10/16/2005 :  20:38:25  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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 ..."
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 10/17/2005 :  08:27:05  Show Profile  Reply with Quote
I'm confused, did you know that using 'þ' would remove all the 'th' before you posted your original reply?
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 10/17/2005 :  08:46:59  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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 ..."
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 10/17/2005 :  08:59:15  Show Profile  Reply with Quote
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

Edited by - Arnold Fribble on 10/17/2005 09:06:54
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 10/17/2005 :  10:01:21  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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 ..."
Go to Top of Page

SQLServerSpotlight
Yak Posting Veteran

Australia
57 Posts

Posted - 01/31/2006 :  19:17:21  Show Profile  Send SQLServerSpotlight a Yahoo! Message  Reply with Quote
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.

Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000