Getting substring from sql column value

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I have a column in a sql table having datatype varchar,I want to get substring from that column

e.g. I have a data like

ID | Job Name
1 | 11-92-23-04
2 | 12-13-04
3 | 12-763-07
4 | 12-67

From these I want to get list seperated by 2nd dash like
11-92
12-13
12-763
12-67

Posted 15-Sep-13 17:37pm

Ravndra22

Updated 15-Sep-13 17:42pm

v2

Add a Solution

3 solutions

Solution 3

If you would like to get parts from string, use this:

SQL

DECLARE @tmp TABLE (ID INT IDENTITY(1,1), [Job Name] VARCHAR(30))

INSERT INTO @tmp ([Job Name])
SELECT '11-92-23-04'
UNION ALL SELECT '12-13-04'
UNION ALL SELECT '12-763-07'
UNION ALL SELECT '12-67'

;WITH Parts AS
(
	SELECT 1 AS PartNo, ID, LEFT([Job Name], CHARINDEX('-',[Job Name])-1) AS Part, RIGHT([Job Name], LEN([Job Name])- CHARINDEX('-',[Job Name])) AS Remainder, [Job Name]
	FROM @tmp
	WHERE CHARINDEX('-',[Job Name])>0
	UNION ALL 
	SELECT PartNo+1 AS PartNo, ID, LEFT([Remainder], CHARINDEX('-',[Remainder])-1) AS Part, RIGHT([Remainder], LEN([Remainder])- CHARINDEX('-',[Remainder])) AS Remainder, [Job Name]
	FROM Parts
	WHERE CHARINDEX('-',[Remainder])>0
	UNION ALL
	SELECT PartNo+1 AS PartNo, ID, Remainder AS Part, NULL AS Remainder, [Job Name]
	FROM Parts
	WHERE CHARINDEX('-',[Remainder])=0
)
SELECT ID, PartNo, Part, [Job Name]
FROM Parts
WHERE PartNo<3
ORDER BY ID, PartNo

Result:

ID	PartNo	Part	[Job Name]
1	1	11	11-92-23-04
1	2	92	11-92-23-04
2	1	12	12-13-04
2	2	13	12-13-04
3	1	12	12-763-07
3	2	763	12-763-07
4	1	12	12-67
4	2	67	12-67

Posted 16-Sep-13 0:15am

Maciej Los

Solution 2

One way is to write a CLR function that uses a RegularExpression.

Using straight SQL for complex string manipulation quickly become tedious.

A better idea is to put the data in the way you want to get it out.

Posted 15-Sep-13 17:53pm

PIEBALDconsult

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ArunRajendra · Accepted Answer · 2013-09-15T17:50:00

Solution 1

Try this code

SQL

select SUBSTRING(JobName,0,CHARINDEX ('-',JobName,CHARINDEX ('-',JobName,0)+1))

SQL

SELECT SUBSTRING(JobName, 0, 
case
WHEN CHARINDEX('-', 
     JobName, CHARINDEX('-', JobName, 0) + 1) 
     > 0 THEN CHARINDEX('-', JobName, CHARINDEX('-', 
     JobName, 0) + 1) ELSE len(JobName) + 1
end
     )
FROM ScheduleMaster)

Posted 15-Sep-13 17:50pm

ArunRajendra

Updated 16-Sep-13 0:24am

v4

Comments

Ravndra22 16-Sep-13 0:43am

thanx.. It really works.. But I have a some string Like 13-1151 or 13-1158-07
and I want 13-1151, 13-1158 from it....Any help please

ArunRajendra 16-Sep-13 1:28am

check the updated query.

ArunRajendra 16-Sep-13 1:29am

Can you please vote :). Remember 5 is highest.

Ravndra22 16-Sep-13 2:24am

That query doesnt work in sql 2005,will you check it for me.

My query

SELECT SUBSTRING(JobName, 1,
WHEN CHARINDEX('-',
JobName, CHARINDEX('-', JobName, 0) + 1)
> 0 THEN CHARINDEX('-', JobName, CHARINDEX('-',
JobName, 0) + 1) ELSE len(JobName) + 1)
FROM ScheduleMaster

ArunRajendra 16-Sep-13 2:37am

This is basic SQL statement it should work in 2005 as well. If you are getting error post will look into it.

Ravndra22 16-Sep-13 2:25am

And plz post 1st query too.

ArunRajendra 16-Sep-13 2:35am

Posted both the query.

Ravndra22 16-Sep-13 6:13am

Error-
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WHEN'.

ArunRajendra 16-Sep-13 6:25am

Check the corrected query in solution.