I have a 3 part key that look like: 2

[ghbeers]

I have a 3 part key that look like:

1234567*AWD*2017/FA
1234345*AWD2*2017/FA
1234555*BDD09*2017/FA

I'm having trouble pulling only the middle value between the 2 delimiters (*) into my report. After trying all sorts of methods, it keeps including the trailing *. When I tried using the first 4 characters in the last component (2017/FA for example), I got some kind of index error. As you can see, the delimited value is not always the same length, although its maximum length is 5.

What's the most straight forward way to do this in a select statement in SQL Server.

Thank you.

 

[johnherman]

You did not state, so I guess * is the delimiter. It's been awhile, but you need to use INSTR to find the location of the first and second *. Untested Syntax:

SUBSTR (keyfield, INSTR(keyfield,'*',1)+1), INSTR(keyfield,'*',(INSTR(keyfield,'*',1)+1)+1)

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[ghbeers]

Thank you for responding. Yes the asterisk is the delimiter. I also failed to mention I'm working in SQL Server. INSTR is not being recognized as a built-in function.

I'm sorry for the misinformation.

 

[Olaf Doschke]

To avoid all kinds of errors turn NULL into an empty string by ISNULL() and also add '**' in case a record has an empty key in it or an old form of key differing from that pattern. Remember you query all data. The moment you find index errors in expressions using a CHARINDEX as start position of SUBSTRING, then you just found a row not fulfilling that pattern, it's as simple as that. CHARINDEX then returns 0 and that doesn't work as starting position of SUBSTRING, for example.

So just look at SELECT key of TABLE WHERE CHARINDEX('*',key)=0 and you'll see keys not having your pattern. Then you may change these or skip them. Nobody can foresee that for you.

Of course, you will never be able to verify millions of records, but a query is able, especially when looking for the antipattern - in this case, an absent delimiter. Because what you can manually overview is the empty result this should yield. And if you find something, you already have your data not fitting the proposed pattern and thus to be fixed or skipped by extending your positive search pattern. That's very general advice about solving such things instead of giving up because of getting errors. Errors are reported to help you, not to torture you.

Bye, Olaf.

 

[SgtJarrow]

I'd start with the original value, then use SUBSTRING to get the correct value.

DECLARE @InputValues TABLE
(
	DataValue		VARCHAR(100)
);

INSERT INTO @InputValues
(DataValue)
VALUES
('1234567*AWD*2017/FA'),
('1234345*AWD2*2017/FA'),
('1234555*BDD09*2017/FA');

SELECT
	iv.DataValue 'OriginalValue',
	LEFT(iv.DataValue, CHARINDEX('*', iv.DataValue) - 1) 'FirstValue',
	SUBSTRING(iv.DataValue, CHARINDEX('*', iv.DataValue) + 1, LEN(iv.DataValue) - CHARINDEX('*', iv.DataValue) - CHARINDEX('*', REVERSE(iv.DataValue)))  'MiddleValue',
	RIGHT(iv.DataValue, CHARINDEX('*', iv.DataValue) - 1) 'LastValue'
FROM @InputValues iv;

Robert "Wizard" Johnson III
U.S. Military Vets MC
Data Integration Engineer

 

[ghbeers]

Thank you SgtJarrow, this is what I needed. I already know the starting value or the middle part, it's always 9. But I was just doing something basically wrong when trying to isolate the middle value. Thank you so much.

 

[DaveInIowa]

This is a cheat and would probably be too dangerous to use, but here it is...

SELECT Value,
       PARSENAME(REPLACE(Value, '*', '.'), 3) LeftPart,
       PARSENAME(REPLACE(Value, '*', '.'), 2) MidPart,
       PARSENAME(REPLACE(Value, '*', '.'), 1) RightPart
  FROM (VALUES ('1234567*AWD*2017/FA'), ('1234345*AWD2*2017/FA'), ('1234555*BDD09*2017/FA')) t(Value)

 

[Olaf Doschke]

PARSENAME risks having '.' in the name. Most probably that's what you mean by 'probably too dangerous'.

But you remind me of STRING_SPLIT() introduced 2016:

DECLARE @InputValues TABLE
(
 DataValue VARCHAR(100) NULL
);

INSERT INTO @InputValues
(DataValue)
VALUES
('1234567*AWD*2017/FA'),
('1234345*AWD2*2017/FA'),
('1234555*BDD09*2017/FA'),
(NULL),
('23232*CCD2'),
('anything goes');

SELECT value FROM @InputValues iv 
CROSS APPLY STRING_SPLIT(ISNULL(iv.DataValue,''), '*') 


SELECT iv.DataValue, tmp.value as midvalue FROM @InputValues iv 
CROSS APPLY 
(SELECT value FROM STRING_SPLIT(ISNULL(iv.DataValue,'')+'*', '*') 
	ORDER BY %%physloc%% 
	OFFSET 1 ROWS
	FETCH NEXT 1 ROW ONLY 
)tmp

The first query illustrates how STRING_SPLIT creates several rows from the singe DataValues, instead of splitting at '.' like PARSENAME, you can split at whatever character you like, also at '*', as here.

The second query makes use of the also new OFFEST LIMIT functionality to get only the second value STRING_SPLIT spits out. That feature is just nagging to have an ORDER BY and you don't want to order by the only column value, you want the order as is, the pseudo column %%physloc%% is helpful here. OFFSET 1 means skipping the first part before the first '*', then FETCHING the next part. To guarantee one '*' I simply add it. Besides, I added some problematic values like NULL or a key having no '*'.

This needs SQL2016.

Bye, Olaf.

 

[johnherman]

Sorry. My bad. In Transact-SQL the command is CHARINDEX that works the same as INSTR in most other variants of SQL.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[DaveInIowa]

@Olaf - Yes, the possibility of embedded '.'s was what I meant.

Now that we have the additional information that each 'good' value will begin with 7 chars/digits and an asterisk, we could employ the following code to pull the values.

SELECT Value,
       CASE
          WHEN Value IS NULL THEN NULL
          WHEN Value LIKE '_______*_%*%' THEN SUBSTRING(Value, 9, CHARINDEX('*', Value, 9) - 9)
          ELSE ''
       END MidPart
  FROM (VALUES ('1234567*AWD*2017/FA'), ('1234345*AWD2*2017/FA'), ('1234555*BDD09*2017/FA'), (NULL), ('some thing')) t(Value)