Removing spaces within a string

I want to remove spaces within a string in a column if there is more than 1
space between 2 words
For ex
ROYAL SCOTLAND --> ROYAL SCOTLAND
AMERICAN EXPRESS --> AMERICAN EXPRESS
DELOITE TOUCHE --> DELOITE TOUCHE
Does anyone have any creative ways of doing the above
Thanks
Nice thing, already discussed in here:
http://groups.google.de/groups?hl=de...gbl%26rnum%3D1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Sanjay" <Sanjay@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B403551F-BD26-4502-9833-51A7A19C819D@.microsoft.com...
>I want to remove spaces within a string in a column if there is more than 1
> space between 2 words
> For ex
> ROYAL SCOTLAND --> ROYAL SCOTLAND
> AMERICAN EXPRESS --> AMERICAN EXPRESS
> DELOITE TOUCHE --> DELOITE TOUCHE
> Does anyone have any creative ways of doing the above
> Thanks

Removing spaces within a string

I want to remove spaces within a string in a column if there is more than 1
space between 2 words
For ex
ROYAL SCOTLAND --> ROYAL SCOTLAND
AMERICAN EXPRESS --> AMERICAN EXPRESS
DELOITE TOUCHE --> DELOITE TOUCHE
Does anyone have any creative ways of doing the above
ThanksNice thing, already discussed in here:
hx.gbl&rnum=1&prev=/ groups%3Fq%3Dblanks%26hl%3Dde%26lr%3D%26
group%3Dmicrosoft.publ
ic.sqlserver.programming%26selm%3DunisU2McEHA.3580%2540TK2MSFTNGP11.phx.gbl%26rnum
%3D1" target="_blank">http://groups.google.de/groups?hl=d...num
%3D1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Sanjay" <Sanjay@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B403551F-BD26-4502-9833-51A7A19C819D@.microsoft.com...
>I want to remove spaces within a string in a column if there is more than 1
> space between 2 words
> For ex
> ROYAL SCOTLAND --> ROYAL SCOTLAND
> AMERICAN EXPRESS --> AMERICAN EXPRESS
> DELOITE TOUCHE --> DELOITE TOUCHE
> Does anyone have any creative ways of doing the above
> Thanks

Removing spaces within a string

I want to remove spaces within a string in a column if there is more than 1
space between 2 words
For ex
ROYAL SCOTLAND --> ROYAL SCOTLAND
AMERICAN EXPRESS --> AMERICAN EXPRESS
DELOITE TOUCHE --> DELOITE TOUCHE
Does anyone have any creative ways of doing the above
ThanksNice thing, already discussed in here:
http://groups.google.de/groups?hl=de&lr=&threadm=unisU2McEHA.3580%40TK2MSFTNGP11.phx.gbl&rnum=1&prev=/groups%3Fq%3Dblanks%26hl%3Dde%26lr%3D%26group%3Dmicrosoft.public.sqlserver.programming%26selm%3DunisU2McEHA.3580%2540TK2MSFTNGP11.phx.gbl%26rnum%3D1
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Sanjay" <Sanjay@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B403551F-BD26-4502-9833-51A7A19C819D@.microsoft.com...
>I want to remove spaces within a string in a column if there is more than 1
> space between 2 words
> For ex
> ROYAL SCOTLAND --> ROYAL SCOTLAND
> AMERICAN EXPRESS --> AMERICAN EXPRESS
> DELOITE TOUCHE --> DELOITE TOUCHE
> Does anyone have any creative ways of doing the above
> Thanks

removing partial string from column

how do i update a table which has like two strings in 1 column like

blog, joe ?

i want to strip the joe into a new field and the blog into another field

update Agency
set firstname= substring(firstname,charindex(' ',firstname)+1 ,len(firstname))

i managed to strip the first name which is the string at the back but not the last name which is the string at the frontTry this to get the lastname:
lastname = left(column1, patindex('%,%', column1) - 1)|||The error message was :-

update Agency
set lastname = left(lastname, patindex('%,%', lastname) - 1)

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

Dario D'Alessandro, Dario
Stan Dickson, Stan
Ray John, Ray
Juan Sanchez, Juan

I trying to remove the Dario|||Gotta make sure there is at least one comma, or it errors out. Try this:

set lastname = left(lastname, charindex(',', lastname + ',') - 1)|||Thanks that did actually work.. not sure how that charindex worked. i was using to REPLACE statements to make it work. It was more efficient using your method.|||If you need a method of parsing name strings into component parts, I have a UDF that will do that too.

Removing characters

I want to remove all non-letter, non-number characters in a string. An example string would be: How#can*I^make this@.work?
I would like it to look like HowcanImakethiswork
I need to do this for a whole field. Any ideas?
ThanksCreate a user-defined function such as this:

create function CharOnly(@.TargetString varchar(500))
returns varchar(500)
as
begin
declare @.NewString varchar(50)
declare @.Counter int
set @.NewString = ''
set @.Counter = 0
while @.Counter < len(@.TargetString)
begin
set @.Counter = @.Counter + 1
if ASCII(UPPER(substring(@.TargetString, @.Counter, 1))) between 65 and 90 set @.NewString = @.NewString + substring(@.TargetString, @.Counter, 1)
end
return @.NewString
end

Then use it in your select statement like this:
select dbo.CharOnly('How#can*I^make this@.work?')

blindman|||If you are looking for a way to process regular expressions, click on the following link:

link (http://www.codeproject.com/database/xp_pcre.asp?target=xp_pcre)

Removing character from varchar

Hello all,
How would you remove the last character from the following string:
jmy0084t?
Thanks
JonJon wrote:
> Hello all,
> How would you remove the last character from the following string:
> jmy0084t?
> Thanks
> Jon
>
select left('jmy0084t',len('jmy0084t')-1)|||Hi
Such as
DECLARE @.str varchar(10)
SET @.str = 'jmy0084t?'
SELECT @.str,LEFT(@.str,LEN(@.str)-1),STUFF(@.str,LEN(@.str),1,'')
John
"Jon" wrote:
> Hello all,
> How would you remove the last character from the following string:
> jmy0084t?
> Thanks
> Jon
>

Removing all end of line character from a string

Hello all,

Im looking for an efficient way to remove all end of line character from a string.

Is there a function to do that or to replace them with another character?

This should help you out:

declare @.string varchar(100),

@.CrLf varchar(2)

set @.String = 'line

with a break'

print @.string

set @.CrLf = char(13) + char(10) -- carriage return + line feed

set @.string = replace(@.string, @.CrLf, '')

print @.string

regards Gert-Jan

removing all but alphanumeric characters from strings

I'm cleaning dirty data. One of the tasks is to remove any characters that
aren't alpha or numeric from a string. Can I use the replace function with a
character expression? I tried replace(@.dirtystring, '[^0-9a-zA-Z]', '') and
it didn't replace anything... any ideas?
here's a good link
http://groups.google.com/groups?q=steve+kass+John!Wa43yn&hl=en&lr=&ie=UTF-8&group=microsoft.public.sqlserver.programming&sel m=3CE46DFB.E728741%40drew.edu&rnum=2
your question is probably better suited for the .programming group over the
..server group.
hth,
Eric
Ann Queue wrote:
> I'm cleaning dirty data. One of the tasks is to remove any
> characters that aren't alpha or numeric from a string. Can I use the
> replace function with a character expression? I tried
> replace(@.dirtystring, '[^0-9a-zA-Z]', '') and it didn't replace
> anything... any ideas?
|||http://www.nigelrivett.net/RemoveNon...haracters.html
"Ann Queue" wrote:

> I'm cleaning dirty data. One of the tasks is to remove any characters that
> aren't alpha or numeric from a string. Can I use the replace function with a
> character expression? I tried replace(@.dirtystring, '[^0-9a-zA-Z]', '') and
> it didn't replace anything... any ideas?

Removing all between [and ]

Hi All,
Does anyone know of a way of removing everything between, and including two
given characters in string using TSQL.
e.g. If my result set returns 'Sample Text [12345]' where 12345 is unknown
text, how can I make this 'Sample Text'
I know this is something perhaps best done at application level, but in this
case I need to do this in the data, before it reaches the app.
Thanks!
Simon.lookup charindex() and substring() in bol
Simon Harris wrote:
> Hi All,
> Does anyone know of a way of removing everything between, and including tw
o
> given characters in string using TSQL.
> e.g. If my result set returns 'Sample Text [12345]' where 12345 is unknown
> text, how can I make this 'Sample Text'
> I know this is something perhaps best done at application level, but in th
is
> case I need to do this in the data, before it reaches the app.
> Thanks!
> Simon.
>|||If you are using SQL Server 2005, you could do this with
a .NET UDF and Regular Expressions:
http://www.eggheadcafe.com/articles...5_clr_regex.asp
Robbe Morris - 2004/2005 Microsoft MVP C#
http://www.eggheadcafe.com/forums/merit.asp
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:%23HkAACQAGHA.312@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> Does anyone know of a way of removing everything between, and including
> two given characters in string using TSQL.
> e.g. If my result set returns 'Sample Text [12345]' where 12345 is unknown
> text, how can I make this 'Sample Text'
> I know this is something perhaps best done at application level, but in
> this case I need to do this in the data, before it reaches the app.
> Thanks!
> Simon.
>|||hi Simon,
Try this out
select substring('Sample Text[12345]',1,charindex('[','Sample
Text[12345]',1)-1) as Result
"Simon Harris" wrote:

> Hi All,
> Does anyone know of a way of removing everything between, and including tw
o
> given characters in string using TSQL.
> e.g. If my result set returns 'Sample Text [12345]' where 12345 is unknown
> text, how can I make this 'Sample Text'
> I know this is something perhaps best done at application level, but in th
is
> case I need to do this in the data, before it reaches the app.
> Thanks!
> Simon.
>
>|||Hi Simon ,
What Manish asked will work but fails for data without any '['
Try This
Select Substring('sample text [12345]', 1, Case When
CharIndex('[','sample text [12345]') > 0 Then CharIndex('[','sample
text [12345]') - 1 Else Len('sample text [12345]') End )
With Warm regards
Jatinder Singh|||Jatinder, your timing could not have been better...I've been trying to work
out what was wrong!! :)
Thanks!!...and to the other guys that replied.
Simon.
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1134646198.240311.227770@.g44g2000cwa.googlegroups.com...
> Hi Simon ,
> What Manish asked will work but fails for data without any '['
> Try This
> Select Substring('sample text [12345]', 1, Case When
> CharIndex('[','sample text [12345]') > 0 Then CharIndex('[','sample
> text [12345]') - 1 Else Len('sample text [12345]') End )
> With Warm regards
> Jatinder Singh
>