I want to return the first few characters before the first two dashes. I tried to use LEFT(COL,CHARINDEX('-',COL)) but its not giving me the expected results. example

12345-12-12
12345-1-12
12345-12
12345-12-1234
12345-A12

Expected results:

12345-12
12345-1
12345-12
12345-12
12345-A12

CodePudding user response：

There are many ways to solve these kinds of string problems... you just need to be creative with the functions SQL Server makes available to find the correct pattern and then usually use substring. Here is one possibility.

The logic is:

1. Find whether the string actually has 2 dashes, because if not it can be left untouched.
2. When there are two dashes, find the distance from the end of the second dash, by reversing the string, and then remove that amount using substring.

select
  case when y.Index1 > 0 then substring(x.[Value],1,len(x.[Value])-y.Index2) else x.[Value] end NewValue
from (
  values
  ('12345-12-12'),
  ('12345-1-12'),
  ('12345-12'),
  ('12345-12-1234'),
  ('12345-A12')
) x ([Value])
cross apply (
  values (patindex('%[-]%[-]%', x.[Value]), patindex('%[-]%', reverse(x.[Value])))
) y (Index1,Index2);

Returns:

CodePudding user response：

Here's a solution based on Getting the second occurrence from charindex function in sql server

create table #a (
    a varchar(50)
)
insert #a
values 
  ('12345-12-12')
, ('12345-1-12')
, ('12345-12')
, ('12345-12-1234')
, ('12345-A12')
, ('12345')
, ('12345-12-123-12345')

select a as 'input'
, CHARINDEX('-', a, 1) as 'index1'
, CHARINDEX('-', a, CHARINDEX('-', a, 1)   1) as 'index2'
, left(a, case when CHARINDEX('-', a, CHARINDEX('-', a, 1)   1) = 0 then len(a) else CHARINDEX('-', a, CHARINDEX('-', a, 1)   1) - 1 end) as 'output'
from #a

drop table #a