Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

SQL Return first four values from delimited string

I'm a bit stuck
trying to return the first four values of string of varying length
Substrings is not consistent and parsename has a 4 limitiation

Sample
US-HVD-PROD-PNO-01-VM
EMEA-HVD-PROD-WGC-01-VM
APAC-HVD-UAT-PNO-02-VM

Need to return

US-HVD-PROD-PNO
EMEA-HVD-PROD-WGC
APAC-HVD-UAT-PNO

Any help much appreciated
0
Mauro Cazabonnet
Asked:
Mauro Cazabonnet
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
wouldn't it be something like this:
DECLARE @value VARCHAR(100) = 'US-HVD-PROD-PNO-01-VM' -- EMEA-HVD-PROD-WGC-01-VM    APAC-HVD-UAT-PNO-02-VM
DECLARE @count INT = 0
DECLARE @start INT = 0

WHILE @count < 4
BEGIN
	SELECT @start, @count
	SET @start = CHARINDEX('-', @value, @start+1)
	IF @start = 0 
		BREAK
	ELSE
		SET @count = @count + 1
END

SELECT @start, SUBSTRING(@value, 0, @start)

Open in new window


you could easily create a UDF from it.
0
 
Mauro CazabonnetSenior Software EngineerAuthor Commented:
Awesome thx!!!!!!
Set up as a UDF....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now