asked on

CASE LEFT or RIGHT side in a string

I have core values like this in one table:

ID Value
1 ABCD-1234

I have 'leg' values like this in another table:

ID Value
10 ABCD
11 1234

The leg values are the children of the core values. I need to be able to return the core data with it associated legs, and this is then written to a new table.

The result for the above example would be this:

coreID legID quantity
1 10 1.0
1 11 -1.0

The join between the two tables is the left side of core.value = leg.value
OR right side of core.value = leg.value.

Quantity is a CASE -- WHEN it's the left side of the hyphen (or the left leg) it is 1
WHEN it is the right side of the hyphen (or the right leg), it is -1.

I know there are a gazillion different ways to CHARINDEX it, and CASE it. But I'm having problems bringing it together.

I can split the core.value, but i need help on the join and the case.

DECLARE @b varchar(50)
SELECT @b = 'ABCD-1234'
SET @b = REPLACE(@b,'-','.')

SELECT
      @b [Value],
      REVERSE(PARSENAME(REVERSE(@b),1))[LeftSide],
      REVERSE(PARSENAME(REVERSE(@b),2))[RightSide]

Dulton

The following could be used because it's based upon the return values of charindex...

to be included, CharIndex has to be found in the string. therefore, greater than 0
..to be a "Left" join, it has to return 1.. anything greater will be a "right" join.

SELECT
ct.coreId
lt.legId
,Cast(CASE WHEN CharIndex(lt.value,ct.value) = 1
THEN 1.0
ELSE -1.0
END AS Float) AS [quantity]
FROM CoreTable AS ct
INNER JOIN LegTable AS lt
ON CharIndex(lt.value,ct.value) > 0

dbaSQL

ASKER

your suggestion returns too many rows. i added value into the logic to give a little better picture of the resultset. you can see the first three records are actually the relevent dataset. the 1st two, however, are the onces that i need back in the result set. not quite sure where the last three are coming from.

coreID value legID value quantity
1 ABCD-1234 10 1234            -1
1 ABCD-1234 11 ABCD            1
1 ABCD-1234 1 ABCD-1234      1
1 ABCD-1234 99 E            -1
1 ABCD-1234 98 23            -1
1 ABCD-1234 97 D      -1

The correct output would be

1 10 1
1 11 -1

SOLUTION

Dulton

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

dbaSQL

ASKER

I am really hoping to do it as simply as a case on the left or right side of the hyphen value, and then a join on one side of the value OR the other. This is going to be used in several other routines for inserts and reporting. I don't think I can get the cte into all of the other logic as easily.

dbaSQL

ASKER

Do you have any suggestions outside of the cte?

Dulton

well.....here's another variation which doesn't make use of cte.

select Ct.coreId
       ,lt.legId
       ,Cast(Case WHEN Left(ct.CoreVal,len(lt.LegVal)) = lt.LegVal THEN 1.0 else -1.0 END AS Float) AS [quantity]
FROM (SELECT coreId, Ltrim(Rtrim([value])) as [CoreVal] FROM CoreTable) AS ct
INNER JOIN
       (SELECT legId, Ltrim(Rtrim([value])) as [LegVal] FROM legTable) AS lt
ON ct.[CoreVal] like lt.LegVal + '-%'
or ct.[CoreVal] like '%-' + lt.LegVal

dbaSQL

ASKER

it runs for quite some time before I kill it. at least the few that i've checked, the result seemingly is accurate, but that runtime is unusable.

Dulton

how many ways can we skin this cat.... haha

select Ct.coreId
,lt.legId
,Cast(Case WHEN lt.LegVal = ct.LeftVal THEN 1.0
                          WHEN lt.LegVal = ct.RIghtVal THEN -1.0 ELSE null END AS Float) AS [quantity]
FROM (SELECT CoreId
                  , Left(CoreVal,DelimLoc-1) AS LeftVal
       ,Right(CoreVal,Len(CoreVal)-[DelimLoc]) AS [RightVal]
                  FROM (SELECT coreId
                               , Ltrim(Rtrim([value])) as [CoreVal]
                               , CHARINDEX('-',Ltrim(Rtrim([value]))) As [DelimLoc]
                          FROM CoreTable) AS c) AS ct
INNER JOIN
(SELECT legId, Ltrim(Rtrim([value])) as [LegVal] FROM legTable) AS lt
ON lt.LegVal IN (ct.LeftVal, ct.RightVal)

dbaSQL

ASKER

i am very grateful for your input, but i'm worried that we may be overcomplicating it. this is the old one. not saying it is better, but I'm trying to show you what i am trying to improve:

SELECT                                                      i1.ID,
      i2.ID [LegID],
      CASE i1.Value WHEN SUBSTRING(i2.Value,1,CHARINDEX('-',i2.Value, 1) - 1) +
      (CASE SUBSTRING(i2.Value, 12, 1) WHEN '-' THEN '!' ELSE '' END) THEN 1 ELSE -1 END [Quantity]
FROM
      table1 i1 INNER JOIN table2 i2
       ON SUBSTRING(i2.Value, 1, CHARINDEX('-',i2.Value, 1) - 1) + (CASE SUBSTRING(i2.Value, 12, 1) WHEN '-' THEN '!' ELSE '' END) = i1.Value
OR
       SUBSTRING(i2.Value,CHARINDEX('-',i2.Value, 1) + 1, LEN(i2.Value) ) + (CASE SUBSTRING(i2.Value, 12, 1) WHEN '_' THEN '' ELSE '!' END) = i1.Value

The join between the two tables is the left side of core.value = leg.value
OR right side of core.value = leg.value. and the quantity is a CASE -- WHEN it's the left side of the hyphen (or the left leg) it is 1, WHEN it is the right side of the hyphen (or the right leg), it is -1.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

PortletPaul

@dbaSQL, would it be possible to get the actual table definitions (including indexes)?

and maybe some further sample data?

Dulton's approach without CTE looks ok, it may have something to do with indexing that caused the performance issue.

ps. I couldn't get your existing query to work straight off
(Invalid length parameter passed to the LEFT or SUBSTRING function)
which indicates the simple sample I'm working with may be inadequate.

dbaSQL

ASKER

Yes, portletpaul, i think that tomorrow i will be able to take some time, and get some definition up here. it will be genericized, of course, but i will show you my tables, and a much better picture of what i am trying to do.

basically, it's new data processing. i have to do a lot of manipulation, and then write the new records. i'm sure it will be much better for me to actually put some definition up here. I will try to do so tonight, but more likely tomorrow. than you both for your patience.

dbaSQL

ASKER

Please excuse the delay, Portlet and Dulton. Many changes in my professional platform since this was posted. I do appreciate your time, but this is no longer applicable.

Thank you for your input.