?
Solved

Abbreviating mutual fund names

Posted on 2011-05-12
20
Medium Priority
?
359 Views
Last Modified: 2012-05-11
I have an SQL table of mutual fund names.

I also have a separate table listing abbreviations.  For example:
International                             Intl.
Equity                                        Eq.
Morgan Stanley Dean Witte    MSDW
JP Morgan Fleming                  JPMF

I would like to be able to do two things.  The first is the most important.
Abbreviate each name as much as possible
Abbreviate all words in the name except for the first n (where I set n) words

For example "(LF) Bond Total Return Eurobank EFG" abbreviates to
"(LF) BD TOR. Eurobank EFG"
0
Comment
Question by:AlHal2
  • 15
  • 5
20 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35746408
hmmm. you have the table with the abbreviations, and given a "name", you want to do all the possible replacements?
sounds like a function that would check the value, and as long as it finds a "long name" in the abbrevians table, replace the string by the abbreviation ...
0
 

Author Comment

by:AlHal2
ID: 35746622
Afraid I need some SQL
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 35748924
you can create SQL functions ...

given the table tblAbbreviations ( fullname varchar(1000), shortname varchar(1000))

example:
create function dbo.GetAbbreviatedName(@fullname varchar(1000))
returns varchar(1000)
as
begin
  declare @full varchar(1000)
  declare @short varchar(1000)

  select top 1 @full = fullname, @short = shortname
    from tblAbbreviations 
  where @fullname like '%' + fullname + '%'

  while @@rowcount > 0
  begin
     set @fullname = replace(@fullname, @full, @short)

    select top 1 @full = fullname, @short = shortname
      from tblAbbreviations 
    where @fullname like '%' + fullname + '%'

  end

  return @fullname 
end 

Open in new window


and you can use that function in your sql:

select t.companyname, dbo.GetAbbreviatedName(t.companyname) shortname
  from yourtable t 

Open in new window


hope this helps

note: untested code, I just wrong the code in here...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:AlHal2
ID: 35749572
Thanks. I won't have access to SQL until Monday.  I'll test it then.
Have a good weekend.
0
 

Author Comment

by:AlHal2
ID: 35768459
I ran this query and it seems to hang.

select  fundclassnamename, dbo.GetAbbreviatedName(fundclassnamename) shortname
  from yourtable
where fundclassnamename='XXX'

It seems to hang.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35768522
this could be due to some entry in the table that replaced to "itself", aka which keeps "matching" ...
create function dbo.GetAbbreviatedName(@fullname varchar(1000))
returns varchar(1000)
as
begin
  declare @full varchar(1000)
  declare @short varchar(1000)
  declare @loop_control int

  set @loop_control = 10

  select top 1 @full = fullname, @short = shortname
    from tblAbbreviations 
  where @fullname like '%' + fullname + '%'

  while @@rowcount > 0 and @loop_control > 0
  begin
     set @loop_control = @loop_control -1
     set @fullname = replace(@fullname, @full, @short)

    select top 1 @full = fullname, @short = shortname
      from tblAbbreviations 
    where @fullname like '%' + fullname + '%'

  end

  return @fullname 
end

Open in new window

0
 

Author Comment

by:AlHal2
ID: 35769020
Try entering the following in tblabbreviations

Full                  Shortened
Fund                FD
Old                   OLD
Balanced         BAL

Now run these 2 funds through the function.  They abbreviate partially only.

Reynolds Opportunity Fund
Reynolds Balanced Fund
0
 

Author Comment

by:AlHal2
ID: 35769112
A few more relevant abbreviations.  I hadn't spotted the dupes in the abbreviations file until now.  Could that be a problem?

FullName                           ShortName
Opportunity Fund      OPF.
Opportunity                           OPPOR.
Opportunity                           OPT
Opportunity Trust      OPT.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35769178
it could be ... not 100% sure....
0
 

Author Comment

by:AlHal2
ID: 35769202
I removed Opportunity                           OPT
No change.
0
 

Author Comment

by:AlHal2
ID: 35769240
Perhaps the code is stopping before getting to the end of the fullname and we need some kind of recursion.  I wouldn't know how to code this.  I may of course be mistaken.
0
 

Author Comment

by:AlHal2
ID: 35769884
I think it was actually more to do with leading and following spaces.  Try this.

-----------
  set @loop_control = 10

  select top 1 @full = fullname, @short = shortname
    from tblAbbreviations with (nolock)
  where @fullname like '% ' + fullname + ' %'
  order by fullname desc --in case the word is the start of a phrase

  while @@rowcount > 0 and @loop_control > 0
  begin
     set @loop_control = @loop_control -1
     set @fullname = replace(@fullname, @full, @short)

    select top 1 @full = fullname, @short = shortname
      from tblAbbreviations with (nolock)  
    where @fullname like '% ' + fullname + ' %' order by fullname desc
end

----
set @loop_control = 10
--  select top 1 @full = fullname, @short = shortname
--    from tblAbbreviations with (nolock)
--  where @fullname like '% ' + fullname + '%'

  while @@rowcount > 0 and @loop_control > 0
  begin
     set @loop_control = @loop_control -1
     set @fullname = replace(@fullname, @full, @short)

    select top 1 @full = fullname, @short = shortname
      from tblAbbreviations with (nolock)  
    where @fullname like '% ' + fullname order by fullname desc
end

----

set @loop_control = 10
 -- select top 1 @full = fullname, @short = shortname
 --   from tblAbbreviations with (nolock)
 -- where @fullname like '%' + fullname + ' %'

  while @@rowcount > 0 and @loop_control > 0
  begin
     set @loop_control = @loop_control -1
     set @fullname = replace(@fullname, @full, @short)

    select top 1 @full = fullname, @short = shortname
      from tblAbbreviations with (nolock)  
    where @fullname like  fullname + ' %' order by fullname desc
end

  return @fullname
end
0
 

Author Comment

by:AlHal2
ID: 35770199
One more change.

  set @loop_control = 10

  select top 1 @full = fullname, @short = shortname
    from tblAbbreviations with (nolock)
  where @fullname like '% ' + fullname + ' %'
  order by fullname desc --in case the word is the start of a phrase

  while @@rowcount > 0 and @loop_control > 0
  begin
     set @loop_control = @loop_control -1
     set @fullname = replace(@fullname, @full, @short)

    select top 1 @full = fullname, @short = shortname
      from tblAbbreviations with (nolock)  
    where @fullname like '% ' + fullname + ' %'
       order by len(fullname) desc, fullname desc
end

----
set @loop_control = 10
--  select top 1 @full = fullname, @short = shortname
--    from tblAbbreviations with (nolock)
--  where @fullname like '% ' + fullname + '%'

  while @@rowcount > 0 and @loop_control > 0
  begin
     set @loop_control = @loop_control -1
     set @fullname = replace(@fullname, @full, @short)

    select top 1 @full = fullname, @short = shortname
      from tblAbbreviations with (nolock)  
    where @fullname like '% ' + fullname
    order by len(fullname) desc, fullname desc
end

----

set @loop_control = 10
 -- select top 1 @full = fullname, @short = shortname
 --   from tblAbbreviations with (nolock)
 -- where @fullname like '%' + fullname + ' %'

  while @@rowcount > 0 and @loop_control > 0
  begin
     set @loop_control = @loop_control -1
     set @fullname = replace(@fullname, @full, @short)

    select top 1 @full = fullname, @short = shortname
      from tblAbbreviations with (nolock)  
    where @fullname like  fullname + ' %'
    order by len(fullname) desc, fullname desc
end

  return @fullname
end
0
 

Author Comment

by:AlHal2
ID: 35770534
Actually this is better.

 set @loop_control = 10

  select top 1 @full = fullname, @short = shortname
    from tblAbbreviations with (nolock)
  where @fullname like '%' + fullname + '%'
  order by len(fullname)desc, fullname desc --in case the word is the start of a phrase

  while @@rowcount > 0 and @loop_control > 0
  begin
     set @loop_control = @loop_control -1
     set @fullname = replace(@fullname, @full, @short)

    select top 1 @full = fullname, @short = shortname
      from tblAbbreviations with (nolock)  
    where @fullname like '%' + fullname + '%'
  order by len(fullname)desc,  fullname desc --in case the word is the start of a phrase
end
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35770970
I see you are working on it ...
working now?
0
 

Author Comment

by:AlHal2
ID: 35772286
In testing.
0
 

Author Comment

by:AlHal2
ID: 35776657
I've found a problem.  Take the word brandy.  Since it contains the string "and" it will be abbreivated to br&ndy.  Any suggestions?
0
 

Assisted Solution

by:AlHal2
AlHal2 earned 0 total points
ID: 35776661
sorry br&y
0
 

Assisted Solution

by:AlHal2
AlHal2 earned 0 total points
ID: 35777762
Here is the latest.
ALTER function [dbo].[GetAbbreviatedName](@fullname varchar(255))
returns varchar(255)
as
begin
  declare @full varchar(255)
  declare @short varchar(255)
  declare @loop_control int

set @fullname=replace(@fullname,';',' ')
set @fullname=replace(@fullname,'-',' ')
set @fullname=replace(@fullname,'(',' ')
set @fullname=replace(@fullname,')',' ')
set @fullname=replace(@fullname,'   ',' ')
set @fullname=replace(@fullname,'  ',' ')
set @fullname=rtrim(@fullname)
set @fullname=ltrim(@fullname)

-----------
  set @loop_control = 10

  select top 1 @full = fullname, @short = shortname
    from tblAbbreviations with (nolock) 

  --Avoid problem where string to be abbreviated is contained within another string
 -- eg don't abbreviate brandy to br&y
  where @fullname like '% ' + fullname + ' %' 
  or (@fullname like  fullname + ' %' and left(fullname,len(fullname))=left(@fullname,len(fullname)))
  or (@fullname like '% ' + fullname and right(fullname,len(fullname))=right(@fullname,len(fullname)))
 
 order by len(fullname)desc, fullname desc --in case the word is part of a phrase

  while @@rowcount > 0 and @loop_control > 0
  begin
     set @loop_control = @loop_control -1
     set @fullname = replace(@fullname, @full, @short)

    select top 1 @full = fullname, @short = shortname
      from tblAbbreviations with (nolock)  
    where @fullname like '% ' + fullname + ' %' 
  or (@fullname like  fullname + ' %' and left(fullname,len(fullname))=left(@fullname,len(fullname)))
  or (@fullname like '% ' + fullname and right(fullname,len(fullname))=right(@fullname,len(fullname)))

  order by len(fullname)desc,  fullname desc --in case the word is part of a phrase
end


  return @fullname 
end

Open in new window

0
 

Author Closing Comment

by:AlHal2
ID: 35829874
I don't quite understand the loops and I had to make changes to do with spacing around the key string, but I would have got nowhere without this.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question