1

It's a SQL instance and the examples are as follows:

Table values to transform:

String is Donald.Duck and the desired result is DDuck String is Mickey.Mouse and the desired result is MMouse

Golden Lion
  • 3,840
  • 2
  • 26
  • 35
TontoDiablo
  • 43
  • 1
  • 1
  • 6
  • 2
    tag with database – OldProgrammer Dec 09 '21 at 17:33
  • 1
    Does this answer your question? [Get everything after and before certain character in SQL Server](https://stackoverflow.com/questions/11010453/get-everything-after-and-before-certain-character-in-sql-server). Please read [how to ask](https://stackoverflow.com/help/how-to-ask) and update your question with more details. What have you tried so far? – WOUNDEDStevenJones Dec 09 '21 at 17:33

1 Answers1

0

you can use a regular expression in sql. it runs slower but it will use the dot.net framework

The regular expression is simple. Look forward and find a period then capture all non space characters proceeding the period.

how to call a regular expression in tsql

https://www.sqlshack.com/t-sql-regex-commands-in-sql-server/

#extract the data to a csv then process it and update back into the database

txt="Donald.Duck and the desired result is DDuck String other strings are Mickey.Mouse and Daffy.Duck"

pattern = re.compile(r'([a-zA-Z]+\.[a-zA-Z]+)')
result=re.findall(pattern,txt)
for item in result:
     front,back=item.split('.')
     txt=re.sub(item, front[0]+'.'+back,txt)

print(txt)

output

 Donald.Duck and the desired result is DDuck String other strings are Mickey.Mouse and Daffy.Duck

Sql steps:

  1. split the words into a table of words by space
  2. fetch each word and do an instr to check for a period
  3. split the front and back word by period
  4. keep the first character of the front word and reassemble and replace the phrase
  5. join the words into a sentence and update the database with the new sentence.
Golden Lion
  • 3,840
  • 2
  • 26
  • 35