How can i replace some set of white space to one white space in between string

Dear Frnd

I have to remove some set of white space in between the string and i have to replace with one white space

eg:- my name is divakar

I have to change the string to like this

my name is divakar.

so how can write sql query for that

Code Snippet

declare @.var varchar(100)

set @.var = 'my name is divakar'

while charindex(' ', @.var) > 0 --two spaces

begin

set @.var = replace(@.var, ' ', ' ') -- first is two spaces, second is one space

end

select @.var

|||

Having a function (recursive) to use it on your query...

Code Snippet

Create Function dbo.ProperWhiteSpace

(

@.String varchar(1000)

)

Returns Varchar(1000)

as

Begin

If CharIndex('',@.String) <> 0

Return dbo.ProperWhiteSpace(Replace(@.String,'', ' '));

Return @.String

End

Go

Select dbo.ProperWhiteSpace('mynameis divakar')

|||

Thx Dale

|||thx Manivannan.D.Sekaran

How can i reclaim the unused space of table ?

use "sp_spaceused" find one table have so many unused space,
i want to reclaim the unused space , How do ?
"DBCC UPDATEUSAGE " cann't do it, can you help me?DBCC SHRINKFILE ,for more details please refer to the BOL
"'" <@.discussions.microsoft.com> wrote in message
news:D965FA0D-92D7-4272-8907-31072261FF13@.microsoft.com...
> use "sp_spaceused" find one table have so many unused space,
> i want to reclaim the unused space , How do ?
> "DBCC UPDATEUSAGE " cann't do it, can you help me?|||That is not work ! i'm so desponded !|||any way else ? help ! please ! That is too difficulty !|||http://www.devx.com/tips/Tip/21762
"'" <@.discussions.microsoft.com> wrote in message
news:3101E153-160E-41A3-9AE9-75DB1CD70E51@.microsoft.com...
> That is not work ! i'm so desponded !|||i try do it, but not work . i thought the table was error, but uesed by "DBC
C
CHECKTABLE" had not found error. please help me !
“Uri Dimant”编写：

> http://www.devx.com/tips/Tip/21762
>
>
>
> "'" <@.discussions.microsoft.com> wrote in message
> news:3101E153-160E-41A3-9AE9-75DB1CD70E51@.microsoft.com...
>
>|||If possible
backup , drop and recreate the table.
"虫_" wrote:

> any way else ? help ! please ! That is too difficulty !|||Hello
A little hard to tell exactly what you are after. It could be you think you
have some fragmentation on a table.
Try running DBCC SHOWCONTIG against the table. As a rough guide if your scan
density is low and your scan fragmentation is high you should rebuild your
table. The easiest way to do this is to rebuild the clustered index on the
table. If the table does not have a clustered index, you can create one and
then drop it.
If that is your problem it may be worth creating a job to do this on a
regular basis.
See BOL for more details.
Hope this helps
John
"虫_" wrote:
[vbcol=seagreen]
> i try do it, but not work . i thought the table was error, but uesed by "D
BCC
> CHECKTABLE" had not found error. please help me !
>
> “Uri Dimant”编写：
>|||Thanks a lot ! i had resolved it . first, truncate table ,the next,transmi
t
data by DTS . i had took so many times because the table has a lot of
records. why not give me a command to reclaim unused table space by sql
server 2000 ? the other database have , example , sybase, oracle..

How can i reclaim the unused space of table ?

use "sp_spaceused" find one table have so many unused space,
i want to reclaim the unused space , How do ?
"DBCC UPDATEUSAGE " cann't do it, can you help me?DBCC SHRINKFILE ,for more details please refer to the BOL
"'" <@.discussions.microsoft.com> wrote in message
news:D965FA0D-92D7-4272-8907-31072261FF13@.microsoft.com...
> use "sp_spaceused" find one table have so many unused space,
> i want to reclaim the unused space , How do ?
> "DBCC UPDATEUSAGE " cann't do it, can you help me?|||That is not work ! i'm so desponded !|||any way else ? help ! please ! That is too difficulty !|||http://www.devx.com/tips/Tip/21762
"'" <@.discussions.microsoft.com> wrote in message
news:3101E153-160E-41A3-9AE9-75DB1CD70E51@.microsoft.com...
> That is not work ! i'm so desponded !|||i try do it, but not work . i thought the table was error, but uesed by "DBCC
CHECKTABLE" had not found error. please help me !
â'Uri Dimantâ'ç¼?å'ï¼?
> http://www.devx.com/tips/Tip/21762
>
>
>
> "'" <@.discussions.microsoft.com> wrote in message
> news:3101E153-160E-41A3-9AE9-75DB1CD70E51@.microsoft.com...
> >
> > That is not work ! i'm so desponded !
>
>|||If possible
backup , drop and recreate the table.
"è?«å­?" wrote:
> any way else ? help ! please ! That is too difficulty !|||Hello
A little hard to tell exactly what you are after. It could be you think you
have some fragmentation on a table.
Try running DBCC SHOWCONTIG against the table. As a rough guide if your scan
density is low and your scan fragmentation is high you should rebuild your
table. The easiest way to do this is to rebuild the clustered index on the
table. If the table does not have a clustered index, you can create one and
then drop it.
If that is your problem it may be worth creating a job to do this on a
regular basis.
See BOL for more details.
Hope this helps
John
"è?«å­?" wrote:
> i try do it, but not work . i thought the table was error, but uesed by "DBCC
> CHECKTABLE" had not found error. please help me !
>
> â'Uri Dimantâ'ç¼?å'ï¼?
> > http://www.devx.com/tips/Tip/21762
> >
> >
> >
> >
> >
> >
> > "'" <@.discussions.microsoft.com> wrote in message
> > news:3101E153-160E-41A3-9AE9-75DB1CD70E51@.microsoft.com...
> > >
> > > That is not work ! i'm so desponded !
> >
> >
> >|||Thanks a lot ! i had resolved it . first, truncate table ,the next,transmit
data by DTS . i had took so many times because the table has a lot of
records. why not give me a command to reclaim unused table space by sql
server 2000 ? the other database have , example , sybase, oracle..

How can i reclaim the unused space of table ?

use "sp_spaceused" find one table have so many unused space,
i want to reclaim the unused space , How do ?
"DBCC UPDATEUSAGE " cann't do it, can you help me?
DBCC SHRINKFILE ,for more details please refer to the BOL
"?" <@.discussions.microsoft.com> wrote in message
news:D965FA0D-92D7-4272-8907-31072261FF13@.microsoft.com...
> use "sp_spaceused" find one table have so many unused space,
> i want to reclaim the unused space , How do ?
> "DBCC UPDATEUSAGE " cann't do it, can you help me?
|||That is not work ! i'm so desponded !
|||any way else ? help ! please ! That is too difficulty !
|||http://www.devx.com/tips/Tip/21762
"?" <@.discussions.microsoft.com> wrote in message
news:3101E153-160E-41A3-9AE9-75DB1CD70E51@.microsoft.com...
> That is not work ! i'm so desponded !
|||i try do it, but not work . i thought the table was error, but uesed by "DBCC
CHECKTABLE" had not found error. please help me !
“Uri Dimant”编写：

> http://www.devx.com/tips/Tip/21762
>
>
>
> "?" <@.discussions.microsoft.com> wrote in message
> news:3101E153-160E-41A3-9AE9-75DB1CD70E51@.microsoft.com...
>
>
|||If possible
backup , drop and recreate the table.
"虫X" wrote:

> any way else ? help ! please ! That is too difficulty !
|||Hello
A little hard to tell exactly what you are after. It could be you think you
have some fragmentation on a table.
Try running DBCC SHOWCONTIG against the table. As a rough guide if your scan
density is low and your scan fragmentation is high you should rebuild your
table. The easiest way to do this is to rebuild the clustered index on the
table. If the table does not have a clustered index, you can create one and
then drop it.
If that is your problem it may be worth creating a job to do this on a
regular basis.
See BOL for more details.
Hope this helps
John
"虫X" wrote:
[vbcol=seagreen]
> i try do it, but not work . i thought the table was error, but uesed by "DBCC
> CHECKTABLE" had not found error. please help me !
>
> “Uri Dimant”编写：
|||Thanks a lot ! i had resolved it . first, truncate table ,the next,transmit
data by DTS . i had took so many times because the table has a lot of
records. why not give me a command to reclaim unused table space by sql
server 2000 ? the other database have , example , sybase, oracle..

How can I know the records in SQL 2005 Table possess size space?

We can know easily a database disk size, but can we konw a table in SQL 2005 database possess size? and more, how can I know the records in SQL 2005 Table possess size space?

Try the link below for sp_helpfile it is a system stored procedure and it is in the Master database. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms174307.aspx

|||

Thanks!

But I think Sp_helpfile don't know single Table or Records size

sp_helpfile (Transact-SQL)

Returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server.

|||

Sorry it is sp_spaceused and there are undocumented system stored proc that you can use. Try the links below for details.

http://www.sqlservercentral.com/columnists/achigrik/sql2000.asp

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1062

http://msdn2.microsoft.com/en-us/library/ms188776.aspx

|||

Many thanks!

How can I know rows used space ? Such as I want to know the user Paul used space

select * from mytable where UserName='Paul'

|||Paul spaced used? Paul plus tables associated to Paul because sp_spaceused is for database objects like tables not users.|||

Sorry!

When system perform the sql select * from mytable where UserName='Paul' , it will return many rows , iI want to know these rows use size space.

|||

I am sorry I cannot help you the space used that concerns my employers are to make sure the transaction log will not run out of space, but I know the max for a row is 8060 which is also a SQL Server page. Try the thread below for the specs per Microsoft.

http://forums.asp.net/thread/1398414.aspx

|||

ok just open enterprise manager select the database then in the summary window just click report then expand disk space used by tables and you should get the info you're looking for as per table.

Have fun!

How can I get rid of the comma in a company name by using sql command?

For example: company_name: ABC company, inc.

I want to get rid of the comma, replace by a space. How the query should be write?

Thanks

replace(ColumnName,',',' ')

example

select replace('ABC,ZXX',',',' ')

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thank You!!!