Extract string before character occurrence.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Extract string before character occurrence.

13 posts, Page 1 of 2

1 2 »»

Extract string before character occurrence.

Rate Topic

Display Mode

Topic Options

Author

Message

MacDaddy

Posted Friday, June 04, 2010 2:33 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 15, 2012 7:55 AM
Points: 24, Visits: 110

Hi,

This is bugging me, the solution must really be easy.

I have a column in a table with a variable length string and I want to extract a substring of everything that comes before the charcter '-'.

So for example if the string is 'E01234/1-1' then I want to return just 'E01234/1'. This would be simple with the substring function if everything before and after the '-' was the same length but unfortunately it's variable.

I have tried substring(FieldName, 1, charindex('-', FieldName) -1) which has worked before in the same situation but this time SQL Server telling me that I've passed and invalid length parameter to the substring function.

Any help appreciated,

Paul

Keep the rubber side down and the shiny side up.

Post #932653

ColdCoffee

Posted Friday, June 04, 2010 2:43 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248, Visits: 5,352

hey there, this might help you.

First lets set-up sample data and table

DECLARE @TABLE TABLE (STRING VARCHAR(100))

INSERT INTO @TABLE (STRING)
          SELECT 'E01234/1-1' 
UNION ALL SELECT 'E01234/1-200' 
UNION ALL SELECT 'E01234/1-3000' 
UNION ALL SELECT 'E01234/1-40000' 
UNION ALL SELECT 'E01234/1-500000' 
UNION ALL SELECT 'E01234/1-6000000' 
UNION ALL SELECT 'E01234/1-70000000' 
UNION ALL SELECT 'E01234/1-800000000'

NOw the code that will strip the string

SELECT LEFT(STRING, CHARINDEX('-',STRING)-1) STRIPPED_STRING FROM @TABLE

Hope this helps you!

Post #932661

Gopi Muluka

Posted Friday, June 04, 2010 2:45 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, August 07, 2012 6:19 PM
Points: 1,480, Visits: 262

Try this

DECLARE @FieldName VARCHAR(50)
SET @FieldName='E01234/1-1'; 
SET @FieldName='E01234/11'; 
SELECT SUBSTRING(@FieldName, 1, CASE WHEN CHARINDEX('-', @FieldName) > 0 THEN CHARINDEX('-', @FieldName)-1
									ELSE LEN(@FieldName)
								END)

Post #932662

Gopi Muluka

Posted Friday, June 04, 2010 2:48 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, August 07, 2012 6:19 PM
Points: 1,480, Visits: 262

ColdCoffee,

Here the reason for the error message he received is whenever there is no '-' found in the string then CHARINDEX('-', @FieldName)-1 will return -1 , which throughs an error
We have to handle this case in code, which I have given in my earlier post Smile

Post #932665

MacDaddy

Posted Friday, June 04, 2010 2:56 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 15, 2012 7:55 AM
Points: 24, Visits: 110

Ah, thank you both!!

See I knew it would be easy but that I was missing some understanding somewhere.

So in actual fact what I was doing would have worked had there been no occurrences in the column that omitted the '-' character?

This is one to remember for future reference for sure as I know it'll come up again.

Thanks again, much appreciated,

Paul

Keep the rubber side down and the shiny side up.

Post #932668

ColdCoffee

Posted Friday, June 04, 2010 3:03 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248, Visits: 5,352

Gopi Muluka (6/4/2010)

ColdCoffee,

Here the reason for the error message he received is whenever there is no '-' found in the string then CHARINDEX('-', @FieldName)-1 will return -1 ,

Taking nothing away from your good code, where i got bit-off is ,by this statement from the OP

So for example if the string is 'E01234/1-1' then I want to return just 'E01234/1'. This would be simple with the substring function if everything before and after the '-' was the same length but unfortunately it's variable

He/She dint say that the "-" wont be pesent, right? The OP was concerned because of the fact that the length of the string before and after "-" is incosistent.. so i thought a simple LEFT would suffice. In any case, your code will work flawlessly capturing misprinted information.

Post #932675

MacDaddy

Posted Friday, June 04, 2010 3:06 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 15, 2012 7:55 AM
Points: 24, Visits: 110

Sorry, my bad. Blush

Keep the rubber side down and the shiny side up.

Post #932679

tpanasuk

Posted Wednesday, October 10, 2012 3:03 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, October 12, 2012 12:35 PM
Points: 1, Visits: 1

This was AMAZING and totally helped me finish a report for someone so quickly. Thank you so much for posting! BigGrin

Post #1371131

vikingDBA

Posted Thursday, October 11, 2012 7:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:19 AM
Points: 189, Visits: 863

I know some will cringe, but here are some UDF's that I have created for this type of manipulation. When you are in a time crunch, they really come in handy.

/* ====================================================================== */
/* Gets the rest of the line after the last input string occurance (any length) */
CREATE FUNCTION dbo.fn_GetRestOfLineAfterLast(@tmval2 varchar(2000),@vlsf varchar(2000))
RETURNS varchar(2000)
AS
BEGIN
DECLARE @spot int
DECLARE @av varchar(2000)
DECLARE @part1 varchar(2000)
DECLARE @part2 varchar(2000)

SET @part1 = ''
SET @part2 = @tmval2
SET @av = @part2

SET @spot = PATINDEX('%' + @vlsf + '%',@part2)
while @spot > 0
BEGIN
-- pull that value into part1
SET @part1 = @part1 + SUBSTRING(@part2,1,@spot + LEN(@vlsf) - 1)

-- reset value of part2
SET @part2 = SUBSTRING(@part2,@spot + LEN(@vlsf),LEN(@part2) - (@spot + LEN(@vlsf) - 1))

-- reset the loop control variable
SET @spot = PATINDEX('%' + @vlsf + '%',@part2)
END

-- otherwise, just returns what was sent in (if never found, @part2 still = @av)
SET @av = @part2
RETURN @av
END
GO

/*
SELECT '>' + dbo.fn_GetRestOfLineAfterLast(' -ER-ERROR','-') + '<'
GO
*/
/* ====================================================================== */
/* Gets the front part of line before the last string occurance (any length) */
CREATE FUNCTION dbo.fn_GetLineThroughLast(@tmval2 varchar(2000),@vlsf varchar(2000))
RETURNS varchar(2000)
AS
BEGIN
DECLARE @spot int
DECLARE @av varchar(2000)
DECLARE @part1 varchar(2000)
DECLARE @part2 varchar(2000)

SET @part1 = ''
SET @part2 = @tmval2
SET @av = @part2

SET @spot = PATINDEX('%' + @vlsf + '%',@part2)
while @spot > 0
BEGIN
-- pull that value into part1
SET @part1 = @part1 + SUBSTRING(@part2,1,@spot + LEN(@vlsf) -1)

-- reset value of part2
SET @part2 = SUBSTRING(@part2,@spot + LEN(@vlsf),LEN(@part2) - (@spot + LEN(@vlsf) -1))

-- reset the loop control variable
SET @spot = PATINDEX('%' + @vlsf + '%',@part2)
END

-- otherwise, just returns what was sent in (if never found, @part2 still = @av)
SET @av = SUBSTRING(@part1,1,LEN(@part1)-LEN(@vlsf)) + @vlsf
RETURN @av
END
GO
/*
SELECT '>' + dbo.fn_GetLineThroughLast(' -ER-ERROR','-') + '<'
SELECT '>' + dbo.fn_GetLineThroughLast(' oskiekf-lwidjoke-kasdofkjeoijIOIJlkdjafOIS-asdfkj','-') + '<'
*/
/* ====================================================================== */
/* Gets the front part of line before the last string occurance (any length) */
CREATE FUNCTION dbo.fn_GetLineBeforeLast(@tmval2 varchar(2000),@vlsf varchar(2000))
RETURNS varchar(2000)
AS
BEGIN
DECLARE @spot int
DECLARE @av varchar(2000)
DECLARE @part1 varchar(2000)
DECLARE @part2 varchar(2000)

SET @part1 = ''
SET @part2 = @tmval2
SET @av = @part2

SET @spot = PATINDEX('%' + @vlsf + '%',@part2)
while @spot > 0
BEGIN
-- pull that value into part1
SET @part1 = @part1 + SUBSTRING(@part2,1,@spot + LEN(@vlsf) - 1)

-- reset value of part2
SET @part2 = SUBSTRING(@part2,@spot + LEN(@vlsf),LEN(@part2) - (@spot + LEN(@vlsf) - 1))

-- reset the loop control variable
SET @spot = PATINDEX('%' + @vlsf + '%',@part2)
END

-- otherwise, just returns what was sent in (if never found, @part2 still = @av)
SET @av = SUBSTRING(@part1,1,LEN(@part1)-LEN(@vlsf))
RETURN @av
END
GO
/*
SELECT '>' + dbo.fn_GetLineBeforeLast(' -ER-ERROR','-') + '<'
SELECT '>' + dbo.fn_GetLineBeforeLast(' oskiekf-lwidjoke-kasdofkjeoijIOIJlkdjafOIS-asdfkj','-') + '<'
*/
/* ====================================================================== */
/* Gets what is in the line before the input string occurance (any length) */
CREATE FUNCTION dbo.fn_GetLineUpToValue(@tmval2 varchar(2000),@vlsf varchar(2000))
RETURNS varchar(2000)
AS
BEGIN
DECLARE @spot int
DECLARE @av varchar(2000)
SET @av=LTRIM(RTRIM(@tmval2))

SET @spot = PATINDEX('%' + @vlsf + '%',@av)
if @spot > 0
SET @av=LTRIM(RTRIM(SUBSTRING(@av,1,@spot-1)))
-- otherwise, just returns what was sent in

RETURN LTRIM(RTRIM(@av))
END
GO
/*
SELECT dbo.fn_GetLineUpToValue('wow, this is very funky, oh yes it is',', oh yes')
*/

Post #1371468

dwain.c

Posted Friday, October 12, 2012 12:08 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 2,340, Visits: 3,167

Gopi Muluka (6/4/2010)

Now I guess that would just depend on what the OP wants returned in case no hyphen is in the string, now wouldn't it?

DECLARE @TABLE TABLE (STRING VARCHAR(100))

INSERT INTO @TABLE (STRING)
          SELECT 'E01234/1-1' 
UNION ALL SELECT 'E01234/1-200' 
UNION ALL SELECT 'E01234/1-3000' 
UNION ALL SELECT 'E01234/1'

SELECT ReturnWholeString=LEFT(STRING + '-', CHARINDEX('-', STRING + '-') - 1)
    ,ReturnEmptyString=LEFT(STRING, 
        CASE CHARINDEX('-', STRING) WHEN 0 THEN 1 ELSE CHARINDEX('-', STRING) END-1)
    ,ReturnNULL=CASE WHEN CHARINDEX('-', STRING) > 0 
                    THEN LEFT(STRING, CHARINDEX('-', STRING) - 1) END
FROM @TABLE

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1371900

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions