Add period after each character in field, except...

[cmmrfrds]

I am looking for an easy way to add a period after each character in a field with some exceptions in an SQL Query.

Examples of the data.
MD = M.D.
DDS = D.D.S.
MD, PhD = M.D., Ph.D.
CN, CRN = C.N., C.R.N.

SQL Server 2000.
Also, what is the way to detect only the lower case characters and position in the field.

Thank you.

 

[gmmastros]

Also, what is the way to detect only the lower case characters and position in the field.

Comparisons on strings are controlled by the strings collation. By default, SQL installs with a case-insensitive collation, which is why 'A'='a'. You can use a case-sensitive collation for the purposes of comparison by specifying a different collation. The following example creates a table variable with hard coded data (for testing purposes). Since I did not specify the collation with declaring the table variable, the collation will match your databases default collation. But, notice the PatIndex function. The first parameter is the string I am looking for, and the 2nd parameter is the string I am looking within (for a match). Notice how I put the Collate clause on the column. A binary collation will use an exact match including accents and the upper/lower case of a letter.

Since this uses a table variable, you can copy paste the code below in to a query window and run it to see how this works.

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values('phd')
Insert Into @Temp Values('PhD')
Insert Into @Temp Values('PHD')

Select PatIndex('%PhD%', Data Collate Latin1_General_BIN)
From   @Temp

You can also use collations with the replace function, like this...

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values('phd')
Insert Into @Temp Values('PhD')
Insert Into @Temp Values('PHD')

Select Data As OriginalString,
       Replace(Data Collate Latin1_General_BIN, 'PhD', 'Ph.D.') As NewString,
       PatIndex('%PhD%', Data Collate Latin1_General_BIN) As CharacterPosition
From   @Temp

Notice that the only row affected is the one where it is upper case P-lower case h-Upper Case D. The other rows are unaffected by the replace function.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I am looking for an easy way to add a period after each character in a field with some exceptions in an SQL Query.

Can you show some sample data. Specifically, the field you are talking about, does it ONLY contain this data, or is there more to it? For example, do you have a NAME column with data like:

[tt]
FullName
-----------------
Donald Duck, DDS
Micky Mouse, PhD
Minnie Mouse, CRN
[/tt]


Or is it like this:

[tt]
Name Certification
-------------- -------------
Donald Duck DDS
Micky Mouse PhD
Minnie Mouse CRN
[/tt]


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cmmrfrds]

The column name is ProvType it contains only the data.

Data is

DDS
MD
MD, PhD
MSSB

Thank you.

 

[simian336]

I would probably just do some replaces

drop table #temp

create table #Temp (Data VarChar(100))
Insert Into #Temp Values('MD')
Insert Into #Temp Values('PhD')
Insert Into #Temp Values('CRN')
Insert Into #Temp Values('RN')
Insert Into #Temp Values('CRN')
Insert Into #Temp Values('MD, PhD')

select * from #temp

update #temp
set data =upper(data)
update #temp
set data =replace(data,'PHD','Ph.D.')

update #temp
set data =replace(data,'MD','M.D.')

update #temp
set data =replace(data,'CRN','C.R.N.')

update #temp
set data =replace(data,'RN','R.N.')

select * from #temp

Simi