我怎样才能重新安排字符串与SQL？ [英] How can I rearrange string with SQL?

问题描述

Declare @CustTotalCount as int
Declare @CustMatchCount as int 
select @CustTotalCount = count(*)  from ENG_CUSTOMERTALLY

select @CustMatchCount = count(*)  from Task  where MPDReference in(
select ENG_CUSTOMERTALLY_CUSTOMERTASKNUMBER from dbo.ENG_CUSTOMERTALLY)

if(@CustTotalCount>@CustMatchCount)
select distinct
 substring(ENG_CUSTOMERMYCROSS_MYTECHNIC_TASK_NO, charindex('-', ENG_CUSTOMERMYCROSS_MYTECHNIC_TASK_NO)
 + 1, 1000)
  from dbo.ENG_CUSTOMERMYCROSS where
 ENG_CUSTOMERMYCROSS_CUSTOMER_NUMBER in(
select ENG_CUSTOMERTALLY_CUSTOMERTASKNUMBER from ENG_CUSTOMERTALLY1
except
select MPDReference from Task )

我可以转换

 - A320-200001-01-1(1)
 - A320-200001-02-1(2)
 - A320-200001-01-1(2)
 - A320-200001-01-1(1)
 - A320-200001-01-1(2)
 - A320-200001-02-1(1)

到

- 200001-01-1(1)
 - 200001-02-1(2)
 - 200001-01-1(2)
 - 200001-01-1(1)
 - 200001-01-1(2)
 - 200001-02-1(1)

但我需要：

- 200001-01-1
 - 200001-02-1
 - 200001-01-1
 - 200001-01-1
 - 200001-01-1
 - 200001-02-1

我如何能做到这一点的SQL和C＃？

How can I do that in SQL and C#?

推荐答案

下面是一个使用 PATINDEX ，可以使用通配符。

Here's a technique that uses PATINDEX, which can use wild cards.

SUBSTRING(ENG_CUSTOMERMYCROSS_MYTECHNIC_TASK_NO,
        PATINDEX('%[0-9]%', ENG_CUSTOMERMYCROSS_MYTECHNIC_TASK_NO),
        PATINDEX('%(%', ENG_CUSTOMERMYCROSS_MYTECHNIC_TASK_NO)
                 - PATINDEX('%[0-9]%', ENG_CUSTOMERMYCROSS_MYTECHNIC_TASK_NO)
                )

开始为你的子是第一个数值（％[0-9]％）的位置。长度值是第一个括号（％（％'）少的起始位置的位置。

The start for your substring is the position of the first numeric value (%[0-9]%). The length value is the position of the first parenthesis ('%(%') less the starting position.

这篇关于我怎样才能重新安排字符串与SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！