June 4, 2010 at 2:33 am
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.
June 4, 2010 at 2:43 am
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!
June 4, 2010 at 2:45 am
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)
June 4, 2010 at 2:48 am
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:-)
June 4, 2010 at 2:56 am
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.
June 4, 2010 at 3:03 am
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.
June 4, 2010 at 3:06 am
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.
Sorry, my bad. :blush:
Keep the rubber side down and the shiny side up.
October 10, 2012 at 3:03 pm
This was AMAZING and totally helped me finish a report for someone so quickly. Thank you so much for posting!:-D
October 11, 2012 at 7:28 am
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 @part1varchar(2000)
DECLARE @part2varchar(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 @part1varchar(2000)
DECLARE @part2varchar(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 @part1varchar(2000)
DECLARE @part2varchar(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')
*/
October 12, 2012 at 12:08 am
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 , which throughs an error
We have to handle this case in code, which I have given in my earlier post:-)
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
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 12, 2012 at 9:13 am
vikingDBA (10/11/2012)
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.
I only cringed when I saw all the loops. You can do the same thing without all the looping. Also the GetLineThroughLast and GetLineBeforeLast fail when the character is not found.
Here is one way to do the same logic as the first 3 functions with no looping.
/* Gets the rest of the line after the last input string occurance (any length) */
CREATE FUNCTION dbo.fn_GetRestOfLineAfterLast_ShortVersion
(
@tmval2 varchar(2000),
@vlsf varchar(2000)
)
RETURNS varchar(2000)
AS begin
declare @RetVal varchar(2000)
if CHARINDEX(@vlsf, @tmval2) > 0
select @RetVal = right(@tmval2, CHARINDEX(@vlsf, REVERSE(@tmval2)) - 1)
else
select @RetVal = @tmval2
return @retVal
end
go
/* Gets the front part of line before the last string occurance (any length) */
create function dbo.fn_GetLineThroughLast_ShortVersion
(
@tmval2 varchar(2000),
@vlsf varchar(2000)
)
RETURNS varchar(2000)
as begin
declare @RetVal varchar(2000)
if CHARINDEX(@vlsf, @tmval2) > 0
select @RetVal = left(@tmval2, LEN(@tmval2) - CHARINDEX(@vlsf, REVERSE(@tmval2)) + 1)
else
select @RetVal = @tmval2
return @retVal
end
go
/* Gets the front part of line before the last string occurance (any length) */
CREATE FUNCTION dbo.fn_GetLineBeforeLast_ShortVersion
(
@tmval2 varchar(2000),
@vlsf varchar(2000)
)
RETURNS varchar(2000)
as begin
declare @RetVal varchar(2000)
if CHARINDEX(@vlsf, @tmval2) > 0
select @RetVal = left(@tmval2, CHARINDEX(@vlsf, REVERSE(@tmval2)) -2 )
else
select @RetVal = @tmval2
return @retVal
end
Now to validate that they work. The two that will fail are commented.
--finds the character
SELECT '>' + dbo.fn_GetRestOfLineAfterLast(' -ER-ERROR','-') + '<'
SELECT '>' + dbo.fn_GetRestOfLineAfterLast_ShortVersion(' -ER-ERROR','-') + '<'
--character not found
SELECT '>' + dbo.fn_GetRestOfLineAfterLast(' -ER-ERROR','*') + '<'
SELECT '>' + dbo.fn_GetRestOfLineAfterLast_ShortVersion(' -ER-ERROR','*') + '<'
--finds the character
SELECT '>' + dbo.fn_GetLineThroughLast(' -ER-ERROR','-') + '<'
SELECT '>' + dbo.fn_GetLineThroughLast_ShortVersion(' -ER-ERROR','-') + '<'
--character not found
SELECT '>' + dbo.fn_GetLineThroughLast(' -ER-ERROR','*') + '<' --this one errors when the character is not found
SELECT '>' + dbo.fn_GetLineThroughLast_ShortVersion(' -ER-ERROR','*') + '<'
--finds the character
SELECT '>' + dbo.fn_GetLineBeforeLast(' -ER-ERROR','-') + '<'
SELECT '>' + dbo.fn_GetLineBeforeLast_ShortVersion(' -ER-ERROR','-') + '<'
--character not found
SELECT '>' + dbo.fn_GetLineBeforeLast(' -ER-ERROR','*') + '<' --this one errors when the character is not found
SELECT '>' + dbo.fn_GetLineBeforeLast_ShortVersion(' -ER-ERROR','*') + '<'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 12, 2012 at 9:16 am
I figured someone would cringe.....
Thank you, Sean. I'll take a look at these. Much appreciated.
October 12, 2012 at 9:18 am
vikingDBA (10/12/2012)
I figured someone would cringe.....Thank you, Sean. I'll take a look at these. Much appreciated.
LOL just a lot simpler way of getting the same thing. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply