-
I cann't move all the ~ away?
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
Last edited by sql; 03-06-2011 at 01:35 AM.
-
why can't use REPLACE function
-
Hi Mak,
what do you mean?
Thank you
________
Buy Silver Surfer
Last edited by sql; 03-06-2011 at 01:35 AM.
-
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)
-
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
Last edited by sql; 03-06-2011 at 01:35 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|