October 10, 2013 at 12:36 pm
I have a situation where at the end of a remarks string, there is anywhere from 1 -9 "char(9)" at the end of the string.
Is there any tried and true function or code that can easily without looping, get rid of those unwanted characters and clean up my remarks code???
HELP....and thank you!!! :w00t:
October 10, 2013 at 12:50 pm
Do you only have those characters at the end of your string? you could simply use REPLACE or if you just want to eliminate the last ones, there's a more complex approach.
Here are some examples:
DECLARE @String char(9), @Final varchar(9)
SELECT @String = 'asdf'
SELECT @Final = @String
PRINT @Final + @String
SELECT @Final = LEFT( @String, 10/*Length + 1*/ - PATINDEX( '%[^]%', REVERSE(@String)))
PRINT @Final + @String
SELECT @Final = REPLACE(@String, CHAR(9), '')
PRINT @Final + @String
October 10, 2013 at 1:05 pm
October 10, 2013 at 1:26 pm
October 10, 2013 at 1:42 pm
Could you post some sample data in consumable format(Insert statements or a CTE)? That would help us to know what would be the best option.
October 10, 2013 at 6:38 pm
If you look at the fourth article in my signature links, therein lies a tool that I think is the Swiss army knife of all string handling functions. It is called PatternSplitCM and it can be applied to your situation by splitting the string on the offending characters, setting the first and last rows returned to an empty string, then reforming the strings using FOR XML PATH[/url].
DECLARE @Garbage CHAR(2) = ' ' + CHAR(9);
WITH SampleData (MyString) AS
(
SELECT 'Here''s a remark with trailing garbage' + ' ' + REPLICATE(CHAR(9),3)
UNION ALL SELECT ' ' + REPLICATE(CHAR(9),3) + 'Here''s a remark with leading garbage'
UNION ALL SELECT 'Here''s a remark with no garbage'
),
SplitOnGarbageChars AS
(
SELECT *
,rn=ROW_NUMBER() OVER (PARTITION BY MyString ORDER BY ItemNumber DESC)
FROM SampleData
CROSS APPLY dbo.PatternSplitCM(MyString, '[' + @Garbage + ']')
)
SELECT MyString
,CleanedString=
((
SELECT CASE WHEN 1 IN (ItemNumber, rn) AND [Matched] = 1 THEN '' ELSE Item END
FROM SplitOnGarbageChars b
WHERE a.MyString = b.MyString
ORDER BY ItemNumber
FOR XML PATH(''), TYPE).value('.','varchar(max)')
)
FROM SplitOnGarbageChars a
GROUP BY MyString;
There may be faster approaches directed specifically at what you need to do, but this will work.
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 15, 2013 at 4:14 pm
The CROSS APPLY and SUBSTRING below should do what you need. Let us know if you need some clarification for getting it into an UPDATE statement.
DECLARE @leading_trailing_chars_to_eliminate varchar(10)
SET @leading_trailing_chars_to_eliminate = CHAR(9) + SPACE(1)
----------------------------------------------------------------------------------------------------
DECLARE @patindex_pattern varchar(20)
SET @patindex_pattern = '%[^' + @leading_trailing_chars_to_eliminate + ']%'
SELECT
'~~' + remarks + '~~' AS [~~remarks_original~~],
'~~' + SUBSTRING(remarks, starting_byte, LEN(remarks) - starting_byte - ending_byte + 2) + '~~' AS [~~remarks_updated~~]
FROM (
SELECT CHAR(9)+CHAR(9)+SPACE(3)+CHAR(9) + 'remarks themselves' + CHAR(9)+SPACE(2)+CHAR(9)+SPACE(1)+CHAR(9) AS remarks
) AS test_data
CROSS APPLY (
SELECT
--find the first not-to-be-eliminated character in the string
PATINDEX(@patindex_pattern, remarks) AS starting_byte,
--find the last not-to-be-eliminated character in the string
PATINDEX(@patindex_pattern, REVERSE(remarks)) AS ending_byte
) AS ca1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 15, 2013 at 6:05 pm
Angelindiego (10/10/2013)
turns out that there are spaces and tabs at the beginning and end of the file. Issue is to strip out all of this and just leave what is valid.
If there are no tabs embedded in the useful data, just replace all occurances of CHAR(9) with nothing and then do an LTRIM/RTRIM to get rid of leading and trailing spaces.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply