blocks|key|219219|text|你可以试试这个SUBSTRING(ColumnName,+4,+CHARINDEX(',',+ColumnName)+-+4)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|219220|entityMap^0|7|1K|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

You can try this one <code>SUBSTRING(ColumnName, 4, CHARINDEX(',', ColumnName) - 4)</code>

blocks|key|258346|text|在Oracle+11g中，它可能会起作用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|258347|REGEXP_SUBSTR(REGEXP_SUBSTR(COLUMN_NAME,++'[%5ECN=]%2B',1,1),'[%5E,OU]%2B',1,1)|code-block|syntax|javascript|258348|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In Oracle 11g, it might work.

<pre><code>REGEXP_SUBSTR(REGEXP_SUBSTR(COLUMN_NAME, '[^CN=]+',1,1),'[^,OU]+',1,1)
</code></pre>

blocks|key|219227|text|我认为必须有一个循环来处理这个问题。下面是将解析出来的SQL+Server函数。(我知道问题中没有指定SQL+Server，但这是一个如何做到这一点的示例。)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|219228|select+dbo.ScrubFieldValue(value)+from+table将返回您正在查找的内容|offset|length|style|CODE|219229|CREATE+FUNCTION+ScrubFieldValue
(
++++@Input+varchar(8000)
)
RETURNS+varchar(8000)
AS
BEGIN
++++DECLARE+@retval+varchar(8000)
++++DECLARE+@charidx+int
++++DECLARE+@remaining+varchar(8000)
++++DECLARE+@current+varchar(8000)
++++DECLARE+@currentLength+int

++++select+@retval+=+''
++++select+@remaining+=+@Input
++++select+@charidx+=+CHARINDEX('CN=',+@remaining,2)

++++while(LEN(@remaining)+>+0)
++++BEGIN
++++++++--strip+current+row+from+remaining
++++++++if+(@charidx+>+0)
++++++++BEGIN
++++++++++++select+@current+=+SUBSTRING(@remaining,+1,+@charidx+-+1)
++++++++END
++++++++else
++++++++BEGIN
++++++++++++select+@current+=+@remaining
++++++++END
++++++++select+@currentLength+=+LEN(@current)
++++++++--+get+current+name
++++++++select+@current+=+SUBSTRING(@current,+4,+CHARINDEX(',OU',+@current)-4)
++++++++select+@retval+=+@retval+%2B+@current+%2B+'+'
++++++++--+strip+off+current+from+remaining
++++++++select+@remaining+=substring(@remaining,@currentLength+%2B+1,
++++++++++++LEN(@remaining)+-++@currentLength)
++++++++select+@charidx+=+CHARINDEX('CN=',+@remaining,2)
++++END
++++RETURN+@retval
END|code-block|syntax|javascript|219230|entityMap^0|0|0|18|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I think there has to be a loop to handle this. Here's SQL Server function that will parse this out. (I know the question didn't specify SQL Server, but it's an example of how it can be done.)

<code>select dbo.ScrubFieldValue(value) from table</code> will return what you're looking for

<pre><code>CREATE FUNCTION ScrubFieldValue
(
 @Input varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
 DECLARE @retval varchar(8000)
 DECLARE @charidx int
 DECLARE @remaining varchar(8000)
 DECLARE @current varchar(8000)
 DECLARE @currentLength int

 select @retval = ''
 select @remaining = @Input
 select @charidx = CHARINDEX('CN=', @remaining,2)

 while(LEN(@remaining) &gt; 0)
 BEGIN
 --strip current row from remaining
 if (@charidx &gt; 0)
 BEGIN
 select @current = SUBSTRING(@remaining, 1, @charidx - 1)
 END
 else
 BEGIN
 select @current = @remaining
 END
 select @currentLength = LEN(@current)
 -- get current name
 select @current = SUBSTRING(@current, 4, CHARINDEX(',OU', @current)-4)
 select @retval = @retval + @current + ' '
 -- strip off current from remaining
 select @remaining =substring(@remaining,@currentLength + 1,
 LEN(@remaining) - @currentLength)
 select @charidx = CHARINDEX('CN=', @remaining,2)
 END
 RETURN @retval
END
</code></pre>

blocks|key|258342|text|在Postgres中，假设名称中不包含,，则可以使用split_part()|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|258343|select+substr(split_part(the_column,+',',+1),+4)
from+...|code-block|syntax|javascript|258344|entityMap^0|J|1|Q|C|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

In Postgres, you could use <code>split_part()</code> assuming no name contains a <code>,</code>

<pre><code>select substr(split_part(the_column, ',', 1), 4)
from ...
</code></pre>

blocks|key|258350|text|在我的Z/OS+DB2版本中，CHARINDEX抛出了一个语法错误。这里有两种方法来解决这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|258351|SUBSTRING(ColumnName,+4,+INSTR(ColumnName,',',1)+-+4)
SUBSTRING(ColumnName,+4,+LOCATE_IN_STRING(ColumnName,',')+-+4)|code-block|syntax|javascript|258352|我要补充的是，该版本为V12R1|258353|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

On my version of DB2 for Z/OS CHARINDEX throws a syntax error. Here are two ways to work around that.

<pre><code>SUBSTRING(ColumnName, 4, INSTR(ColumnName,',',1) - 4)
SUBSTRING(ColumnName, 4, LOCATE_IN_STRING(ColumnName,',') - 4)
</code></pre>

I should add that the version is V12R1

blocks|key|219222|text|适用于LUW的Db2+11.x：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|219223|with+tab+(str)+as+(values
++'++CN++=+Tom+Chess++,+OU+=+records,DC=1234564786_data+for+testing,+1234567'
,+'CN=Jack+Bauer,OU=records,DC=1234564786_data+for+testing,+1234567'
,+'CN=John+Snow,OU=records,DC=1234564786_data+for+testing,+1234567'
,+'CN=Anna+Rodriguez,OU=records,DC=1234564786_data+for+testing,+1234567'
)
select+REGEXP_REPLACE(str,+'%5E\s*CN\s*=\s*(.*)\s*,\s*OU\s*=.*',+'\1')
from+tab;|code-block|syntax|javascript|219224|请注意，这种正则表达式模式允许任意数量的空格，如上面示例的第1条记录所示。|219225|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Db2 11.x for LUW: 

<pre><code>with tab (str) as (values
 ' CN = Tom Chess , OU = records,DC=1234564786_data for testing, 1234567'
, 'CN=Jack Bauer,OU=records,DC=1234564786_data for testing, 1234567'
, 'CN=John Snow,OU=records,DC=1234564786_data for testing, 1234567'
, 'CN=Anna Rodriguez,OU=records,DC=1234564786_data for testing, 1234567'
)
select REGEXP_REPLACE(str, '^\s*CN\s*=\s*(.*)\s*,\s*OU\s*=.*', '\1')
from tab;
</code></pre>

Note, that such a regex pattern allows an arbitrary number of spaces as in the 1-st record of example above.

blocks|key|219232|text|如果输入字符串格式良好(例如，看起来像您的样本数据，没有任何额外的标记，如空格)，您可以使用如下内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|219233|substr(str,locate('CN=',+str)%2Blength('CN='),+locate(',',+str)-length('CN=')-1)|code-block|syntax|javascript|219234|如果您Db2版本支持REGEXP，那么这是一个更好的选择。|219235|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If input str is wellformed (i.e. looks like your sample data without any additional tokens such as space), you could use something like:

<pre><code>substr(str,locate('CN=', str)+length('CN='), locate(',', str)-length('CN=')-1)
</code></pre>

If your Db2 version support REGEXP, that's a better choice.

In SQL how can I remove (from displaying on my report no deleting from database) the first 3 characters (CN=) and everything after the comma that is followed by "OU" so that I am left with the name and last name in the same column? for example:

<pre class="lang-none prettyprint-override"><code>CN=Tom Chess,OU=records,DC=1234564786_data for testing, 1234567
CN=Jack Bauer,OU=records,DC=1234564786_data for testing, 1234567
CN=John Snow,OU=records,DC=1234564786_data for testing, 1234567
CN=Anna Rodriguez,OU=records,DC=1234564786_data for testing, 1234567
</code></pre>

Desired display:

<pre class="lang-none prettyprint-override"><code>Tom Chess
Jack Bauer
John Snow
Anna Rodriguez
</code></pre>

I tried playing with TRIM but I don't know how to do it without declaring the position and with names and last names having different lengths I really don't know how to handle that. 

Thank you in advance

Update: I wonder about an approach of using Locate to match the position of the comma and then feed that to a sub-string. Not sure if a approach like would work and not sure how to put the syntax together. What do you think? will it be a feasible approach?

in SQL how can I remove the first 3 characters on the left and everything on the right after an specific character

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

在SQL中，我如何删除(在我的报告中显示no deleting from database)前3个字符(CN=)以及逗号后面跟"OU“的所有内容，以便在同一列中保留姓名和姓氏？例如：CN=Tom Chess,OU=records,DC=1234564786_data for testing, 1234567CN=Jac...

问在SQL中，如何删除左侧的前3个字符以及右侧某个特定字符之后的所有字符
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL中，如何删除左侧的前3个字符以及右侧某个特定字符之后的所有字符EN