I cann't move all the ~ away?

[sql]

Hi all, I use the following code to take away space, ~ and number, but I can't take way all the ~ so I can not counter the sequences letter. what is the mistake I made?

Declare @UserText varchar(7000)

Set @UserText ='1 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

51 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

101 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

151 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

201 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

251 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

301 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

351 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

401 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

451 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

501 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

551 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

601 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

651 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

701 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

751 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

801 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

851 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

901 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~A ATGGGTCTGA

951 GGATCAGTTC ATCCTTTAGC TTTGGAGGCT TCACTTTCAA AAGAACAAGT

1001 GGATCATCCG TCAAGAAGGA AGAGGAAGTG CTTACAGGCA ACCTCCAAAC

1051 ATTGAAAATA AGAGTACATG AGGGGTATGA GGAATTCACA ATGGTTGGGC

1101 GGAGGGCAAC AGCTATCCTG AGGAAAGCAA CTAGAAGGCT GATTCAGTTG

1151 ATAGTAAGTG GAAGAGACGA ACAATCAATC GCTGAGGCAA TCATTGTAGC

1201 AATGGTGTTC TCACAGGAGG ATTGCATGAT AAAGGCAGTC CGAGGCGATC

1251 TGAATTTCGT AAACAGAGCA AACCAAAGAT TAAACCCCAT GCATCAACTC

1301 CTGAGACATT TTCAAAAGGA TGCAAAAGTG CTATTTCAGA ATTGGGGAAT

1351 TGAACCCATT GATAATGTCA TGGGGATGAT CGGAATATTA CCTGACATGA

1401 CTCCCAGCAC AGAAATGTCA CTGAGAGGAG TAAGAGTTAG TAAAATGGGA

1451 GTGGATGAAT ATTCCAGCAC TGAGAGAGTA GTTGTAAGTA TTGACCGTTT

1501 CTTAAGGGTT CGAGATCAGC GGGGGAACGT ACTCTTATCT CCCGAAGAGG

1551 TCAGCGAAAC CCAGGGAACA GAGAAATTGA CAATAACATA CTCATCATCA

1601 ATGATGTGGG AAATCAACGG TCCTGAGTCA GTGCTTGTTA ACACCTATCA

1651 ATGGATCATC AGAAACTGGG AGACTGTGAA GATTCAATGG TCTCAAGACC

1701 CCACGATGCT GTACAATAAG ATGGAGTTTG AACCGTTCCA ATCCTTGGTA

1751 CCTAAAGCTG CCAGAGGTCA ATACAGTGGA TTTGTGAGAA CACTATTCCA

1801 ACAAATGCGT GACGTACTGG GGACATTTGA TACTGTCCAG ATAATAAAGC

1851 TGCTACCATT TGCAGCAGCC CCACCGGAGC AGAGCAGAAT GCAGTTTTCT

1901 TCTTTAACTG TGAATGTGAG AGGCTCAGGG ATGAGAATAC TCGTAAGGGG

1951 CAATTCCCCT GTGTTCAACT ACAATAAGGC AACCAAAAGG CTTACCGTTC

2001 TTGGAAAGGA CGCAGGTGCA TTAACAGAGG ATCCAGATGA GGGGACAGCC

2051 GGAGTGGAAT CTGCAGTGCT GAGGGGATTC CTAATTCTAG GCAAGGAGGA

2101 CAAAAGATAT GGACCAGCAT TGAGCATCAA TGAACTGAGC AATCTTGCGA

2151 AAGGGGAGAA AGCTAATGTG CTGATAGGGC AAGGAGACGT GGTGTTGGTA

2201 ATGAAACGGA AACGGGACTC TAGCATACTT ~~~~~~~~~~ ~~~~~~~~~~

2251 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

2301 ~'





declare @counter smallint
declare @len1 smallint
declare @char1 char

set @counter=0
set @len1 = len(@UserText)

While @counter <=@len1
begin
set @char1 =substring(@UserText,@counter,1)
if (ascii(@char1) between 48 and 57) or (ascii(@char1)=32) or(ascii(@char1)=126)



Begin
Select @UserText = Replace(@UserText,@char1 , '')


End
Set @counter = @counter + 1
end

select @UserText
select len(@UserText)
________
Buy Volcano Vaporizer

[MAK]

why can't use REPLACE function

[sql]

Hi Mak,
what do you mean?
Thank you
________
Buy Silver Surfer

[MAK]

Try this...


Declare @UserText varchar(7000)
Declare @UserText2 varchar(7000)
declare @counter smallint
declare @len1 smallint
declare @char1 char
declare @x bit
set @UserText2 =''
Set @UserText ='1 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

51 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

101 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

151 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

201 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

251 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

301 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

351 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

401 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

451 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

501 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

551 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

601 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

651 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

701 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

751 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

801 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

851 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

901 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~A ATGGGTCTGA

951 GGATCAGTTC ATCCTTTAGC TTTGGAGGCT TCACTTTCAA AAGAACAAGT

1001 GGATCATCCG TCAAGAAGGA AGAGGAAGTG CTTACAGGCA ACCTCCAAAC

1051 ATTGAAAATA AGAGTACATG AGGGGTATGA GGAATTCACA ATGGTTGGGC

1101 GGAGGGCAAC AGCTATCCTG AGGAAAGCAA CTAGAAGGCT GATTCAGTTG

1151 ATAGTAAGTG GAAGAGACGA ACAATCAATC GCTGAGGCAA TCATTGTAGC

1201 AATGGTGTTC TCACAGGAGG ATTGCATGAT AAAGGCAGTC CGAGGCGATC

1251 TGAATTTCGT AAACAGAGCA AACCAAAGAT TAAACCCCAT GCATCAACTC

1301 CTGAGACATT TTCAAAAGGA TGCAAAAGTG CTATTTCAGA ATTGGGGAAT

1351 TGAACCCATT GATAATGTCA TGGGGATGAT CGGAATATTA CCTGACATGA

1401 CTCCCAGCAC AGAAATGTCA CTGAGAGGAG TAAGAGTTAG TAAAATGGGA

1451 GTGGATGAAT ATTCCAGCAC TGAGAGAGTA GTTGTAAGTA TTGACCGTTT

1501 CTTAAGGGTT CGAGATCAGC GGGGGAACGT ACTCTTATCT CCCGAAGAGG

1551 TCAGCGAAAC CCAGGGAACA GAGAAATTGA CAATAACATA CTCATCATCA

1601 ATGATGTGGG AAATCAACGG TCCTGAGTCA GTGCTTGTTA ACACCTATCA

1651 ATGGATCATC AGAAACTGGG AGACTGTGAA GATTCAATGG TCTCAAGACC

1701 CCACGATGCT GTACAATAAG ATGGAGTTTG AACCGTTCCA ATCCTTGGTA

1751 CCTAAAGCTG CCAGAGGTCA ATACAGTGGA TTTGTGAGAA CACTATTCCA

1801 ACAAATGCGT GACGTACTGG GGACATTTGA TACTGTCCAG ATAATAAAGC

1851 TGCTACCATT TGCAGCAGCC CCACCGGAGC AGAGCAGAAT GCAGTTTTCT

1901 TCTTTAACTG TGAATGTGAG AGGCTCAGGG ATGAGAATAC TCGTAAGGGG

1951 CAATTCCCCT GTGTTCAACT ACAATAAGGC AACCAAAAGG CTTACCGTTC

2001 TTGGAAAGGA CGCAGGTGCA TTAACAGAGG ATCCAGATGA GGGGACAGCC

2051 GGAGTGGAAT CTGCAGTGCT GAGGGGATTC CTAATTCTAG GCAAGGAGGA

2101 CAAAAGATAT GGACCAGCAT TGAGCATCAA TGAACTGAGC AATCTTGCGA

2151 AAGGGGAGAA AGCTAATGTG CTGATAGGGC AAGGAGACGT GGTGTTGGTA

2201 ATGAAACGGA AACGGGACTC TAGCATACTT ~~~~~~~~~~ ~~~~~~~~~~

2251 ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~

2301 ~'
set @counter=0
set @len1 = datalength(@UserText)
While @counter <=@len1
begin
set @char1 =substring(@UserText,@counter,1)

if (ascii(@char1) between 48 and 57) or (ascii(@char1)=32) or(ascii(@char1)=126)
begin
set @x=0
end
else
begin
Set @UserText2 = @UserText2 +@char1
end

Set @counter = @counter + 1

end
print '-------------------------'
print @UserText2
print len(@UserText2)

[sql]

HI Mak,

got same result as me
actually the problem are
1) have space before the result
2) can not got the currect len
Thank you
________
Exhaust ultimate power valve