How can I rearrange string with SQL?

Question

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 )

I can convert

• 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)

TO

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

But I need to :

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

How can I do that in SQL and C#?

Answer 1

Is the pattern always the same, if so you could just use SUBSTRING to pull out the bit you want.

EDIT: To take in additional stuff asked in http://stackoverflow.com/questions/3634274/how-can-i-use-substring-in-sql

You could

SELECT DISTINCT SUBSTRING(....) FROM ...

Answer 2

Try substring and len, this sample cuts first 6 and last 4 (4 = 10-6) chars

declare @var varchar(50)
set @var = 'A320-200001-01-1(1)
select substring(@var, 6, len(@var) - 10)

output: 200001-01

In c#, functions are similar, exept zero-based index:

    string var = "A320-200001-01-1(1)";
    var = var.Substring(5, var.Length - 8);
    Console.WriteLine(var);

Answer 3

as answered above, use the SUBSTRING method like you are but use a length of 11 instead of 1000 as long as the data is always in the format you show above.

In C# it would be:

string s = "A320-20001-01-1(1)";
string result = s.Substring(s.IndexOf('-'), 11);

again this is assuming the part you want is always 11 characters. Otherwise if it is always the first '(' you want to end before, you the IndexOf method/function again to find the end index and subtract the first index

Answer 4

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)
                )

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.