select midvalue of a string - SQL Server Forums

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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

select midvalue of a string

Author

Topic

deekshan
Starting Member

5 Posts

Posted - 2008-09-22 : 03:24:30

my cell value is "mcd\categories\aa\finance.htm."
the first part(mcd\categories) are same for column...Remaining values differs for other cells in the column..
I want to display the value after the categories but before the next slash..
For example:
my output for this cell is "aa"

Pls help me to write the query to display the value.
Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-22 : 03:30:13

[code]DECLARE @Sample TABLE
(
Col1 VARCHAR(200)
)

INSERT @Sample
SELECT 'mcd\categories\aa\finance.htm'

SELECT Col1,
REVERSE(PARSENAME(REPLACE(REVERSE(SUBSTRING(Col1, 16, 200)), '\', '.'), 1))
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-22 : 03:57:30


SELECT	Col1,
	PARSENAME(REPLACE(REPLACE(Col1,'.', ''),'\','.'), 2)
FROM	@Sample

Madhivanan

Failing to plan is Planning to fail

deekshan
Starting Member

5 Posts

Posted - 2008-09-22 : 05:07:54

Thanks for your reply.
But its returning NULL

Eg:
My column is

\MCD\Categories\InfoKiosk\Communication\1.htm
\MCD\Categories\InfoKiosk\Communication\2.htm
\MCD\Categories\Corporate Guidelines\Booking Form for Penthouse at JTC Summit.doc
\MCD\Categories\Corporate Identity\guidelines_for_use_of_new_jtc_lo.htm

My output should be:
InfoKiosk
InfoKiosk
Corporate Guidelines
Corporate Identity

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 05:16:34

quote:
Originally posted by deekshan

Thanks for your reply.
But its returning NULL

Eg:
My column is

\MCD\Categories\InfoKiosk\Communication\1.htm
\MCD\Categories\InfoKiosk\Communication\2.htm
\MCD\Categories\Corporate Guidelines\Booking Form for Penthouse at JTC Summit.doc
\MCD\Categories\Corporate Identity\guidelines_for_use_of_new_jtc_lo.htm

My output should be:
InfoKiosk
InfoKiosk
Corporate Guidelines
Corporate Identity

Thanks in advance

SELECT LEFT(REPLACE(col,'\MCD\Categories\',''),CHARINDEX('\',REPLACE(col,'\MCD\Categories\',''))-1) FROM YourTable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-22 : 05:21:13

quote:
Originally posted by deekshan

Thanks for your reply.
But its returning NULL

Eg:
My column is

\MCD\Categories\InfoKiosk\Communication\1.htm
\MCD\Categories\InfoKiosk\Communication\2.htm
\MCD\Categories\Corporate Guidelines\Booking Form for Penthouse at JTC Summit.doc
\MCD\Categories\Corporate Identity\guidelines_for_use_of_new_jtc_lo.htm

My output should be:
InfoKiosk
InfoKiosk
Corporate Guidelines
Corporate Identity

Thanks in advance

In your question you didnt specify that it would start with \

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-22 : 05:24:36


SELECT	Col1,
	PARSENAME(REPLACE(REPLACE(SUBSTRING(Col1,2,LEN(Col1)),'.', ''),'\','.'), 2)
FROM	@Sample

Madhivanan

Failing to plan is Planning to fail

deekshan
Starting Member

5 Posts

Posted - 2008-09-22 : 05:33:57

Thanks for your reply.
I got the output.
If MCD is the value which has been passed as an parameter in the stored procedure then how the statement will be.
Actually MCD is the parameter which i pass before executing the stored procedure.
That part i have completed before. but its a variable one which varies for different departments as per my input parameter(MCD is the department name)

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 05:44:25

quote:
Originally posted by deekshan

Thanks for your reply.
I got the output.
If MCD is the value which has been passed as an parameter in the stored procedure then how the statement will be.
Actually MCD is the parameter which i pass before executing the stored procedure.
That part i have completed before. but its a variable one which varies for different departments as per my input parameter(MCD is the department name)

Thanks in advance

what should be your output when you pass MCD as deptname?

deekshan
Starting Member

5 Posts

Posted - 2008-09-22 : 06:00:36

Output must be same. It should be after categories and '\'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 06:05:06