to get substring of string (urgent)

[557]

how can i get the characters in a string to the 1st hyphen to 3rd hyphen in string

eg:- if string is

345-895454-534443-3454354-4543

i need to get

895454-534443


can anyone please help

 

[LFCfan]

declare @v varchar(500)
declare @c varchar(100)
declare @start int, @end int

set @v = '345-895454-534443-3454354-453'
set @start = charindex('-', @v, 1)
set @end = charindex('-', @v, charindex('-', @v, 1) +1, charindex('-', @v, 1) +1) +1)

select @start, @end
set @c = substring(@v, @start+1, @end-@start-1)
select @c

Hope this helps, there may be typos as I am not on a PC with SQL Server, so I can't test this, but you get the idea, anyway!

 

[dbomrrsm]

This should do it:

declare @v varchar(500),
@firstpos int,
@secondstring varchar (500),
@secondpos int,
@thirdstring varchar (500),
@thirdpos int,
@result varchar (500)
set @v = '345-895454-534443-3454354-453'
set @firstpos = charindex('-', @v, 1)
set @secondstring = substring (@v,@firstpos+1,500)
set @secondpos = charindex('-', @secondstring, 1)
set @thirdstring = substring(@secondstring,@secondpos+1,500)
set @thirdpos = charindex('-', @thirdstring, 1)
set @result = substring(@v,@firstpos+1,(@secondpos+@thirdpos)-1)
print @firstpos
print @secondstring
print @secondpos
print @thirdstring
print @thirdpos
print @result

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

Created a proc to deal with the above problems with a string:

Only tested where the "mark" is one char long i.e. a single comma,dash etc.

Input is the text (or column), the mark (in the examples case a dash) - which occurance of the mark you want to start at, and which occurence of the mark you want to stop at - see below proc code for example.

SP Code:

CREATE PROC [dbo].[sp_BetweenTwoMarks] (@Text varchar(8000), @Pattern varchar(1), @first int, @last int)
AS
BEGIN
	SET NOCOUNT ON
    	DECLARE @Matches INT
    	DECLARE @Index INT
    	DECLARE @MaxIndex INT
	DECLARE @PatternLen INT
	DECLARE @substr1 INT
	DECLARE @substr2 INT
	DECLARE @lastcopy INT
	DECLARE @Result VARCHAR (8000)
	DECLARE @Error VARCHAR (200)
	SET @lastcopy = @last
    	SET @PatternLen = Len(@Pattern)
    	SET @MaxIndex = Len(@Text) -- (@PatternLen - 1)
    	SET @Index = 1
    	SET @Matches = 0
    	WHILE @Index <= @MaxIndex
    			BEGIN
    				IF SUBSTRING(@Text, @Index, @PatternLen) = @Pattern SET @Matches = @Matches + 1
				IF @matches = @first SET @substr1 = @index + 1
				IF @matches = @first SET @first = 0
				IF @matches = @last SET @substr2 = @index - @substr1
				IF @matches = @last SET @last = 0
				SET @Index = @Index + 1
			END
		IF @matches < @lastcopy
		RAISERROR ('You requested a last match count of %d but there are only %d matches.' ,16,1,@lastcopy,@matches)
		SELECT SUBSTRING(@Text,@substr1,@substr2)
END

Example use for data '345-895454-534443-3454354-4543' where we are interested in the "mark" of - and what is between the first and third occurance of the "mark".

EXEC dbo.sp_BetweenTwoMarks '345-895454-534443-3454354-4543' ,'-',1,3

Result

895454-534443

Example with comma from mark 2,5

EXEC dbo.sp_BetweenTwoMarks '345,895454,534443,345,4354,4543' ,',',2,5

Result

534443,345,4354


If a last occurence of the mark is outside the number of occurances of the mark an error is returned:

EXEC dbo.sp_BetweenTwoMarks '345,895454,534443,345,4354,4543' ,',',2,7

Returns

Server: Msg 50000, Level 16, State 1, Procedure sp_BetweenTwoMarks, Line 29
You requested a last match count of 7 but there are only 5 matches.

hope this helps someone

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

Function version to use with columns:

CREATE  FUNCTION [dbo].[BetweenTwoMarks] (@Text varchar(8000), @Pattern varchar(20), @first int, @last int)
RETURNS VARCHAR (8000)
AS
BEGIN
    	DECLARE @Matches INT
    	DECLARE @Index INT
    	DECLARE @MaxIndex INT
	DECLARE @PatternLen INT
	DECLARE @substr1 INT
	DECLARE @substr2 INT
	DECLARE @lastcopy INT
	DECLARE @Result VARCHAR (8000)
	DECLARE @Error VARCHAR (200)
	SET @lastcopy = @last
    	SET @PatternLen = Len(@Pattern)
    	SET @MaxIndex = Len(@Text) -- (@PatternLen - 1)
    	SET @Index = 1
    	SET @Matches = 0
    	WHILE @Index <= @MaxIndex
    		BEGIN
    		IF SUBSTRING(@Text, @Index, @PatternLen) = @Pattern SET @Matches = @Matches + 1
		IF @matches = @first SET @substr1 = @index + 1
		IF @matches = @first SET @first = 0
		IF @matches = @last SET @substr2 = @index - @substr1
		IF @matches = @last SET @last = 0
		SET @Index = @Index + 1
		END
		IF @matches < @lastcopy
		BEGIN
		SET @Error = ('You requested a last match count of '+cast(@lastcopy as varchar (10))+' but there are only '+cast(@matches as varchar (10))+'matches.')+1
		END
		SET @Result =  (SELECT SUBSTRING(@Text,@substr1,@substr2))
	RETURN @Result
END

[blue]DBomrrsm[/blue]

 

[557]

thanks everyone