INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

I have a 3 part key that look like:

I have a 3 part key that look like:

(OP)
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.

RE: I have a 3 part key that look like:

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


RE: I have a 3 part key that look like:

(OP)
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.

RE: I have a 3 part key that look like:

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.

RE: I have a 3 part key that look like:

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

CODE

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

RE: I have a 3 part key that look like:

(OP)
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.

RE: I have a 3 part key that look like:

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

CODE

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) 

RE: I have a 3 part key that look like:

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:

CODE

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.

RE: I have a 3 part key that look like:

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


RE: I have a 3 part key that look like:

@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.

CODE

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) 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close