June 17, 2009 at 3:54 pm
I have records for few columns which have data suffixed with _Error . I am trying to trim the _Error from all these columns for all records that have this. Whats the best approach for removing '_Error' from all the records in different cols.
ex if i have recs with name as
ABC_error
CDB
XYZ
YUY
ROP_error
i expect the result to be
ABC
CDB
XYZ
YUY
ROP
Thanks
June 17, 2009 at 3:58 pm
DECLARE @Table TABLE (RowValue varchar(15))
INSERT INTO @Table
SELECT 'ABC_error' UNION ALL
SELECT 'CDB' UNION ALL
SELECT 'XYZ' UNION ALL
SELECT 'YUY' UNION ALL
SELECT 'ROP_error'
SELECT REPLACE(RowValue,'_error','')
FROM @Table
April 3, 2012 at 11:40 am
I have almost the same situation with two important differences:
(1) the trailing characters, after the underscore, are of variable length from row to row and
(2) I need to trim after the SECOND underscore.. The row values will look something like this
AIT_FIT_projecto and moonbeat
AIT_FIT_projectofile where handled
AIT_FIT_pritibabe every way
What approach will allow me to retain the first two groups of acronyms, and only shave off the second underscore and string?
-
April 3, 2012 at 12:49 pm
This is a 3 year old thread. It might be better to start your own since your issue is certainly a bit different. That being said, it depends. There are several ways to make this happen and it all depends on the shape of your data. Start a new thread, post some ddl and sample data. The benefit to you is that you will receive tested code in return.
_______________________________________________________________
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/
April 3, 2012 at 1:07 pm
OK, I will. I mistook the last entry's 'Last Login' to be the timestamp of the entry itself....which is why I thought it was a recent thread.:Whistling:
-
April 3, 2012 at 7:06 pm
Old or new, this is pretty easy:
DECLARE @d TABLE (data VARCHAR(max))
INSERT INTO @d (data)
SELECT 'AIT_FIT_projecto and moonbeat'
UNION ALL SELECT 'AIT_FIT_projectofile where handled'
UNION ALL SELECT 'AIT_FIT_pritibabe every way'
SELECT SUBSTRING(data, 1, CHARINDEX('_', data)) +
SUBSTRING(data, 1+LEN(SUBSTRING(data, 1, CHARINDEX('_', data)))
,CHARINDEX('_', SUBSTRING(data, 1+CHARINDEX('_', data),LEN(data)))-1)
FROM @d
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
April 4, 2012 at 7:06 am
dwain.c (4/3/2012)
Old or new, this is pretty easy:
DECLARE @d TABLE (data VARCHAR(max))INSERT INTO @d (data)
SELECT 'AIT_FIT_projecto and moonbeat'
UNION ALL SELECT 'AIT_FIT_projectofile where handled'
UNION ALL SELECT 'AIT_FIT_pritibabe every way'
SELECT SUBSTRING(data, 1, CHARINDEX('_', data)) +
SUBSTRING(data, 1+LEN(SUBSTRING(data, 1, CHARINDEX('_', data)))
,CHARINDEX('_', SUBSTRING(data, 1+CHARINDEX('_', data),LEN(data)))-1)
FROM @d
Actually if you look at the other thread it is not always so simple. 🙂 http://qa.sqlservercentral.com/Forums/Topic1277586-338-1.aspx
Your code works great IF the data is always in the perfect format. It simply crashes if there are not at least two underscores, and there a number of other ways this can not work correctly.
_______________________________________________________________
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/
April 4, 2012 at 7:25 am
Bad data? When do we ever store bad data in our databases?
Might work if you changed all data to data+'__' though.
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
April 4, 2012 at 7:27 am
Bad data? When do we ever store bad data in our databases?
BWAA HAAA HAAA!!!!!
_______________________________________________________________
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/
April 4, 2012 at 10:05 am
If it helps, here are a couple of functions I created to do just that. First input is the full line of information to look at, and the second input is the string to search for.
You can modify to fit your needs.
--Gets rest of line after last occurance of input string (second input).
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
--test run
SELECT '>' + dbo.fn_GetRestOfLineAfterLast(' -ER-ERROR','-') + '<'
GO
--this gets everything on the line before the last occurance of input string (second input).
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
--Test runs
SELECT '>' + dbo.fn_GetLineBeforeLast(' -ER-ERROR','-') + '<'
SELECT '>' + dbo.fn_GetLineBeforeLast(' oskiekf-lwidjoke-kasdofkjeoijIOIJlkdjafOIS-asdfkj','-') + '<'
April 4, 2012 at 12:36 pm
Thank vikingDBA, I printed and have up on my cubicle for reference. With all the data clean up ahead, I will take a look and see where to use. For now I have implemented solution on the other thread.
http://qa.sqlservercentral.com/Forums/Topic1277586-338-1.aspx
-
April 4, 2012 at 12:39 pm
vikingDBA (4/4/2012)
If it helps, here are a couple of functions I created to do just that. First input is the full line of information to look at, and the second input is the string to search for.You can modify to fit your needs.
--Gets rest of line after last occurance of input string (second input).
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
--test run
SELECT '>' + dbo.fn_GetRestOfLineAfterLast(' -ER-ERROR','-') + '<'
GO
--this gets everything on the line before the last occurance of input string (second input).
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
--Test runs
SELECT '>' + dbo.fn_GetLineBeforeLast(' -ER-ERROR','-') + '<'
SELECT '>' + dbo.fn_GetLineBeforeLast(' oskiekf-lwidjoke-kasdofkjeoijIOIJlkdjafOIS-asdfkj','-') + '<'
If I get a chance I'll look into improving this code. Right now, this code isn't very scalable.
April 4, 2012 at 12:45 pm
Lynn:
I look forward to seeing that. I do light to medium design work, mostly administration. My databases that I write are only used by small departments. I could use some lessons in scalability! 🙂
April 4, 2012 at 12:48 pm
vikingDBA (4/4/2012)
Lynn:I look forward to seeing that. I do light to medium design work, mostly administration. My databases that I write are only used by small departments. I could use some lessons in scalability! 🙂
For some light reading, this is a blog post I did a few years ago, Comparing Hardcoded functions, In-Line TVF's, and Scalar Functions[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply