• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

Abbreviating mutual fund names

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
AlHal2
Asked:
AlHal2
  • 15
  • 5
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
AlHal2Author Commented:
Afraid I need some SQL
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
AlHal2Author Commented:
Thanks. I won't have access to SQL until Monday.  I'll test it then.
Have a good weekend.
0
 
AlHal2Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
AlHal2Author Commented:
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
 
AlHal2Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it could be ... not 100% sure....
0
 
AlHal2Author Commented:
I removed Opportunity                           OPT
No change.
0
 
AlHal2Author Commented:
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
 
AlHal2Author Commented:
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
 
AlHal2Author Commented:
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
 
AlHal2Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see you are working on it ...
working now?
0
 
AlHal2Author Commented:
In testing.
0
 
AlHal2Author Commented:
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
 
AlHal2Author Commented:
sorry br&y
0
 
AlHal2Author Commented:
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
 
AlHal2Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 15
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now