sql - 在以SQL Server中的特定字符开头的字段中查找文本的特定部分(finding specific part of text in a field which starts with a specific character in SQL server)

Question

I want to write an inner join where Table_1's identifier is an unique field and Table_2's field which is being used for the join has the identifier value embedded in a text and that text starts with "DJ_" .

(我想编写一个内部联接，其中Table_1的标识符是唯一字段，而用于联接的Table_2的字段具有嵌入在文本中的标识符值，并且该文本以“ DJ_”开头。)

Here, from Table_2 I want to extract the identifier value from those tuples which start only with "DJ_" , not any other text.

(在这里，我想从Table_2中从仅以“ DJ_”开头而不是任何其他文本的那些元组中提取标识符值。)

For example, the Table_1's identifier value for the join is 12345. The same identifier is a part of a text field in Table_2;

(例如，联接的Table_1的标识符值为12345。相同的标识符是Table_2中文本字段的一部分；)

the text looks like "DJ_12_XYZ_1_YTR_12345.trt" .

(文本看起来像“ DJ_12_XYZ_1_YTR_12345.trt” 。)

So, I want the text before and after "12345" to be removed while joining it as Table_2's identifier.

(因此，我希望将“ 12345”之前和之后的文本删除，同时将其作为Table_2的标识符加入。)

Thanks for your help.

(谢谢你的帮助。)

  ask by Krishna Teja translate from so

Answer 1

You have an easy problem and a harder problem.

(您有一个简单的问题，一个较困难的问题。)

To get the records you want to join that start with DJ_...

(要获取要加入的记录，以DJ_开头...)

a simple where LEFT(string,3) = 'DJ_'

(一个简单的where LEFT(string,3) = 'DJ_')

Now getting the number out.

(现在获取号码。)

This one is fun...

(这个很有趣...)

substring(right('DJ_12_XYZ_1_YTR_12345.trt',charindex('_',reverse('DJ_12_XYZ_1_YTR_12345.trt'),1)),2,5)