String index function (substring / charindex)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

String index function (substring / charindex)

Author

Topic

michael.fisher
Starting Member

3 Posts

Posted - 2013-08-05 : 16:18:44

Hi, In need of help with an SQL Query. What i need is to be able to extract specific characters from data.

Eg name

1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results

i.e. 1.2-KPIA1-App-00001 1.2 KPIA1

*I need this in part of a script as there is thousands of rows of data.

Please help?!?!?!?!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-05 : 16:24:21

Will "1.2" always be 3 characters? Or do you need everything before the first dash?

And for "KPIA1", will it always be 5 characters after the first dash? Or do you need some random number of characters between the first and second dash?

Please provide some additional sample data to help us help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

michael.fisher
Starting Member

3 Posts

Posted - 2013-08-05 : 16:31:07

Thanks for your reply, need this for tomorrow morning :(

1.2 (3 characters) will not always be the same, there will be some instances of 5 characters, e.g. 2.1.1

KPIA1 (5 characters) will not always be the same, there may be some instances of 6 characters, eg KPIC10.

so, examples may include;

1.2-KPIA1-App-00001
2.1.1-KPIA2-APP-00008
2.4-KPIC10-App-00010

michael.fisher
Starting Member

3 Posts

Posted - 2013-08-05 : 16:46:42

Any luck?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-05 : 16:49:32

Here is an example you can use. You can copy this to an SSMS window and run it. In your actual query, use just the select statement and replace the x with your actual column name

CREATE TABLE #tmp(x VARCHAR(256));
INSERT INTO #tmp VALUES 
('1.2-KPIA1-App-00001'),
('2.1.1-KPIA2-APP-00008'),
('2.4-KPIC10-App-00010');


SELECT
	LEFT(x,CHARINDEX('-',x+'-')-1),
	LEFT( STUFF(x,1,CHARINDEX('-',x+'-'),''), CHARINDEX('-',STUFF(x,1,CHARINDEX('-',x+'-'),'')+'-')-1)
FROM
	#tmp;
	
DROP TABLE #tmp;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources