take away space when inser data

[sql]

Hi all,
if I want to insert group of data into database and take awary the space what can I do ?

thank you
________
Silversurfer reviews

[skhanal]

Can you elaborate what you mean by take away space?

If you want to see space used by table increased, write a loop to insert millions of rows in the table. You can also insert BLOBs to see the effect quickly.

[Rawhide]

If you mean remove the blank space character from the data that you are inserting, you can use the Replace() function to replace the spaces with nothing or some other character.

If you only want to remove leading or trailing spaces, then use the RTrim and LTrim functions (or if doing it in VB or VB Script, you can use the Trim function).

[sql]

Hi,
I have some data like
51 TTCAAAAGAA CAAGTGGGTC ATCTGTCAAG AAGGAAGAGG AAGTACTTAC.
and I want to take the number and space between letter, do i have anything can do in sql?

thank you
________
Umberto Maglioli

[Rawhide]

Use Replace().

Declare @UserText varchar(1000)

Set @UserText = '51 TTCAAAAGAA CAAGTGGGTC ATCTGTCAAG AAGGAAGAGG AAGTACTTAC'

Select Replace(@UserText, space(1), '')

Result: 51TTCAAAAGAACAAGTGGGTCATCTGTCAAGAAGGAAGAGGAAGTACTT AC


---------------------------------

Declare @UserText varchar(1000)

Set @UserText = '51 TTCAAAAGAA CAAGTGGGTC ATCTGTCAAG AAGGAAGAGG AAGTACTTAC'

Select Replace(Replace(@UserText, '1', '')@UserText, '5', '')

Result: TTCAAAAGAA CAAGTGGGTC ATCTGTCAAG AAGGAAGAGG


--------------------

Put it all together:

Declare @UserText varchar(1000)

Set @UserText = '51 TTCAAAAGAA CAAGTGGGTC ATCTGTCAAG AAGGAAGAGG AAGTACTTAC'

Select @UserText = Replace(@UserText, space(1), '')

Declare @counter int
Set @counter = 0

While @counter < 10
Begin
Select @UserText = Replace(@UserText, @counter, '')

Set @counter = @counter + 1
End

Select @UserText


Result: TTCAAAAGAACAAGTGGGTCATCTGTCAAGAAGGAAGAGGAAGTACTTAC

[sql]

Hi Rawhide,
It work , but I still have question at
Select @UserText = Replace(@UserText, @counter, '')
replace function is use string expression3 to replace expression2, so it is easy to understand
step one Select Replace(@UserText, space(1), '') take away any space,
step 2 Declare @UserText varchar(1000)

Set @UserText = '51 TTCAAAAGAA CAAGTGGGTC ATCTGTCAAG AAGGAAGAGG AAGTACTTAC'

Select Replace(Replace(@UserText, '1', '5', '') take away number.
But how to understand @UserText = Replace(@UserText, @counter, '')
Thank you
________
Mywebcamhookup

[Rawhide]

@counter is a counter. It will start at 0 and every time we loop through the whil statement, it will increment by 1. It will continue this until it reaches 10. So it will cycle through 0 to 9.

So the first cycle, @counter = 0. This means that

Replace(@UserText, @counter, '')

is the same expression as

Replace(@UserText, 0, '')


This replaces each single digit integer 0 through 9 one at a time.

[sql]

Thak you, but it still lppks like remove everry 1- to 9 letters.
Thank you
________
DRUGTEST

[Rawhide]

It doesn't remove the letters in position 1 through 9, it removes the literal numbers from the string.

[sql]

HI all,
I try to change Make's programer
to find all letters, is that make sences.?


drop function dbo.udf_findallletters
go

Create function dbo.udf_findallletters (@inputstring varchar(7000))
returns varchar(6000)
as
begin

declare @count1 smallint
declare @len1 smallint
declare @word varchar(7000)
declare @char1 char
--Assignment
set @word=''
set @count1=1
set @len1 = datalength(@inputstring)

While @count1 <=@len1
begin
set @char1 =substring(@inputstring,@count1,1)
if( ascii(@char1) between 65 and 90) or (( ascii(@char1) between 97 and 122))
begin
set @word=@word+substring(@inputstring,@count1,1)
end
set @count1=@count1+1
end
return convert(varchar(6000),@word)

end
________
Genetically modified food

[sql]

Hi the
dbo.udf_findallletters function have
the follow problem
1) change upcase
2) how to insert the letter result into table?
Thank you
________
Ford feature model specifications