Solved

SQL Return first four values from delimited string

Posted on 2016-07-22
2
34 Views
Last Modified: 2016-07-22
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
Comment
Question by:Mauro Cazabonnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 41724857
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
 
LVL 4

Author Closing Comment

by:Mauro Cazabonnet
ID: 41724978
Awesome thx!!!!!!
Set up as a UDF....
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question