June 5, 2009 at 2:39 am
Hi All
I got in a situation where i have to clean my table which is backs to an year worth of data, My problem is that i have a Varchar column in the table, i need to find the length of the varchar column, i cant use Len ( Column1),
The problem is that i have to count some characters as Length 2 instead of 1, say for example if i find character 'A' on the string then its considered as length 2.
I wrote a function that loops through the string and checks each character by character. This is time and resource consuming.
Is there any better way of doing this on 2005 or on 2008, the examples are as below
Declare @Text1 Varchar(max)
Set @Text1 ='Test Message'
Condition is that 'E' and 'A' must be counted as 2
If i do select len(text1) i will get a result 12, but i the result i should get is 15 ( that is 12 +2+1 (2 instances of ''E' and '1' instance of a))
Can you please help me on this
Thanks in advance for your help
Cheers
June 5, 2009 at 2:45 am
Did you try LEN(REPLACE(@charstring, 'A', 'AA') ?
Does it perform better?
-- Gianluca Sartori
June 5, 2009 at 2:55 am
Using a numbers/tally table
--Pre-populate with characters of length > 1
DECLARE @Lengths TABLE(Ch CHAR(1) PRIMARY KEY, Length INT)
INSERT INTO @Lengths(Ch,Length)
SELECT 'A',2 UNION ALL
SELECT 'E',2
Declare @Text1 Varchar(max)
Set @Text1 ='Test Message'
SELECT SUM(COALESCE(l.Length,1))
FROM Numbers n
LEFT OUTER JOIN @Lengths l ON l.Ch=SUBSTRING(@Text1,n.Number,1)
WHERE n.Number BETWEEN 1 AND LEN(@Text1)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537June 5, 2009 at 3:16 am
Thanks Guys
I cant really get the numbers table, can you please throw some light on the Numbers tables which is involved on the join 🙂
June 5, 2009 at 3:18 am
Thanks mate, there are some ascii characters which i need to check them , its a good idea, but i need to implement this on the live services at some point
June 5, 2009 at 3:25 am
CrazyMan (6/5/2009)
Thanks GuysI cant really get the numbers table, can you please throw some light on the Numbers tables which is involved on the join 🙂
Apologies, should have added a link to this
http://www.sqlservercentral.com/articles/T-SQL/62867/
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537June 5, 2009 at 3:49 am
Thanks Mark, this is an interesting new dimension, Looks great, i will work on this and let you know my result
Cheers
🙂
June 5, 2009 at 4:04 am
Will this work?
Declare @Text1 Varchar(max)
Set @Text1 ='Test Message'
select len(@text1)+(LEN(@text1) - LEN(REPLACE(@text1, 'e', '')))+(LEN(@text1) - LEN(REPLACE(@text1, 'a', '')))
"Don't limit your challenges, challenge your limits"
June 5, 2009 at 1:49 pm
This question completely depends on WHY you need to count A's and E's as double characters. Is English the native language for this database? Because if UNICODE characters are the reasons why you need to consider 2 bytes per character only for some characters, you may be going down the wrong path.
June 5, 2009 at 8:59 pm
As Aaron suggests, you might want to let us know why you need to do this so we can ensure you get the best solution.
In the mean time and as big a fan I am of the Tally or Numbers table, I believe that Gianluca Sartori's may be the quickest provided that the doubling of the key characters doesn't cause an overflow of the datatype used.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply