用sql中的单个实例替换字符的多个实例 [英] Replace multiple instance of a character with a single instance in sql

问题描述

可能是重复的，但我找不到解决方案.

Probably it's a duplicate, but I couldn't find a solution.

要求:

我有以下字符串:

Heelloo
Heeelloo
Heeeelloo
Heeeeeelloo
Heeeeeeeelloo
.
.
.
Heeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeelloo

预期输出: Hello

在 SQL 中实现这一目标的最佳方法是什么?

What is the best way to achieve this in SQL?

我使用的版本:

Microsoft SQL Server 2012 - 10.0.7365.0 (X64) Jul 28 2015 00:39:54 Copyright (c) 
Microsoft Corporation Parallel Data Warehouse (64-bit) on Windows NT 6.2 <X64> 
(Build 9200: )

推荐答案

有一个很好的技巧可以删除单个字母的重复项:

There is a nice trick for removing such duplicates for a single letter:

select replace(replace(replace(col, 'e', '<>'
                              ), '><', ''
                      ), '<>', 'e'
              )

这确实需要两个不在字符串中(或更具体地说，不在彼此相邻的字符串中)的字符(<"和>").特定的字符并不重要.

This does require two characters ("<" and ">") that are not in the string (or more specifically, not in the string next to each other). The particular characters are not important.

这是如何工作的?

Heeello
H<><><>llo
H<>llo
Hello

这篇关于用sql中的单个实例替换字符的多个实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！