四、使用数据处理函数——文本处理函数(2)

朱雀 2023-06-22 05:28 126阅读 0赞

## 四、使用数据处理函数——文本处理函数(2) ##

**1.trim()：去除数据左右空格**

**(1)为什么数据会存在左右空格？**

有些数据可能是爬虫爬取的，数据清洗没有做好，就会存在左右空格。比如下面的数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70][]

**(2)实例**

**示例1：**

去除name列的左右空格并重命名为new\_name。

select * from fruit1;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 1][]

**示例2：**

去除name列的左右空格并重命名为new\_name。【选择所有数据】

select *,trim(name) as new_name from fruit;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 2][]

**2.ltrim():去除数据左空格**

**3.rtrim():去除数据右空格**

**4.left():返回文本串左边的字符**

**示例1：**

返回文本左边的第一个字符

select *,left(name,1) from fruit1;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 3][]

**示例2：**

返回文本左边的前两个字符

select *,left(name,2) from fruit1;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 4][]

**5.right():返回文本串右边的字符**

**示例1：**

返回文本右边的两个字符。

select *,right(name,2) from fruit1;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 5][]

**6.length():返回文本串的长度【字节】**

**示例1：**

返回name列的长度。【字节长度】

select *,length(name) from fruit1;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 6][]

**7.char\_length():返回文本串的长度【字符】**

**示例1：**

返回name列的长度。【字符长度】

select *,char_length(name) from fruit1;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 7][]

**8.locate()：返回子串在文本串中的位置**

**示例1：**

返回"瓜"字在name中的位置。

select name,locate("瓜",name) from fruit1;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 8][]

**9.lower()：将字符串变为小写**

**10.upper():将字符串变为大写**

**11.总结：**

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;width:231px;">函数</td> 
   <td style="text-align:center;vertical-align:middle;width:267px;">描述</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;width:231px;">trim</td> 
   <td style="text-align:center;vertical-align:middle;width:267px;">去除数据左右空格</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;width:231px;">ltrim</td> 
   <td style="text-align:center;vertical-align:middle;width:267px;">去除数据左空格</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;width:231px;">rtrim</td> 
   <td style="text-align:center;vertical-align:middle;width:267px;">去除数据右空格</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;width:231px;">left</td> 
   <td style="text-align:center;vertical-align:middle;width:267px;">返回文本串左边的字符</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;width:231px;">right</td> 
   <td style="text-align:center;vertical-align:middle;width:267px;">返回文本串右边的字符</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;width:231px;">length</td> 
   <td style="text-align:center;vertical-align:middle;width:267px;">返回文本串的字节长度</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;width:231px;">char_length</td> 
   <td style="text-align:center;vertical-align:middle;width:267px;">返回文本串的字符长度</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;width:231px;">locate</td> 
   <td style="text-align:center;vertical-align:middle;width:267px;">返回子串在文本串中的位置</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;width:231px;">lower</td> 
   <td style="text-align:center;vertical-align:middle;width:267px;">将字符串变为小写</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;width:231px;">upper</td> 
   <td style="text-align:center;vertical-align:middle;width:267px;">将字符串变为大写</td> 
  </tr> 
 </tbody> 
</table>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191214142220765.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2019121415183273.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191214152023741.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2019121415212887.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191214152254329.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20191214152700141.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/2019121415310655.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20191214153343715.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20191214153742618.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70