Home:ALL Converter>How to return characters after two dashes when present

How to return characters after two dashes when present

Ask Time:2021-09-22T05:17:16         Author:Sam332

Json Formatter

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

Author:Sam332,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/69275647/how-to-return-characters-after-two-dashes-when-present
Dale K :

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.\nThe logic is:\n\nFind whether the string actually has 2 dashes, because if not it can be left untouched.\nWhen 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.\n\nselect\n case when y.Index1 > 0 then substring(x.[Value],1,len(x.[Value])-y.Index2) else x.[Value] end NewValue\nfrom (\n values\n ('12345-12-12'),\n ('12345-1-12'),\n ('12345-12'),\n ('12345-12-1234'),\n ('12345-A12')\n) x ([Value])\ncross apply (\n values (patindex('%[-]%[-]%', x.[Value]), patindex('%[-]%', reverse(x.[Value])))\n) y (Index1,Index2);\n\nReturns:\n\n\n\n\nNewValue\n\n\n\n\n12345-12\n\n\n12345-1\n\n\n12345-12\n\n\n12345-12\n\n\n12345-A12\n\n\n",
2021-09-21T21:23:30
yy