如何在Oracle中使用填充函数 [英] how to use stuff function in Oracle

问题描述

节日快乐！

几个月前，我在Oralce的问题中询问了填充函数，并想再次讨论这个问题.问题是使用WM_CONCAT函数(在Oracle中)时，结果在我要填充的字段的前面或前面包含(，).我用谷歌搜索找到了一个解决方案，但是到目前为止没有成功.为了删除我要填充的字段的多余的空字符串，我需要在WM_CONCAT函数中包括什么?有人可以调查一下吗?

I asked the stuff function in Oralce question several months ago and would like to revisit this question. The problem was when using the WM_CONCAT function (in Oracle), the result included the (,) in the front or before the field I wanted to stuff. I googled to find a solution for this but had no success so far. What should I need to include in the WM_CONCAT function in order to remove the extra empty string for the field i'd like to stuff? Could someone please look into?

谢谢， 乔

我下面有一些示例记录

CREATE TABLE #TEMP
(
  COMPANY VARCHAR (50),
  ID INT,
  PRODUCT VARCHAR(50)
)

INSERT INTO #TEMP VALUES ('APPLES', '123', 'IPHONE4')
INSERT INTO #TEMP VALUES ('APPLES', '123', 'IPHONE4S')
INSERT INTO #TEMP VALUES ('APPLES', '123', 'IPHONE5')


INSERT INTO #TEMP VALUES ('SAMSUNG', '124', 'GALAXY S2')
INSERT INTO #TEMP VALUES ('SAMSUNG', '124', 'GALAXY S3')
INSERT INTO #TEMP VALUES ('SAMSUNG', '124', 'GALAXY S4')


INSERT INTO #TEMP VALUES ('NOKIA', '125', 'C5-02')
INSERT INTO #TEMP VALUES ('NOKIA', '125', 'C5-03')
INSERT INTO #TEMP VALUES ('NOKIA', '125', 'C5-04')

运行此查询时，其生成为

When running this query it produces as

SELECT
COMPANY,
ID,
WM_CONCAT(PRODUCT) AS PRODUCT, --STUFF PRODUCT COLUMN
FROM
(
   SELECT
   COMPANY,
   ID,
   PRODUCT
   FROM #TEMP
 )
GROUP BY COMPANY, ID


COMPANY     ID      PRODUCT
APPLES      123     IPHONE4, IPHONE4S, IPHONE5
SAMSUNG     124     ,GALAXY S2, GALAXY S3, GALAXY S4
NOKIA       125     ,C5-02, C5-03, C5-04

推荐答案

只需替换:

WM_CONCAT(PRODUCT)

具有:

LTRIM(WM_CONCAT(PRODUCT),',')

这将从您的列中删除所有前导逗号.

This will remove any leading comma's from your column.

这篇关于如何在Oracle中使用填充函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！