Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do delete multiple CHAR(9) at end of string Expand / Collapse
Author
Message
Posted Thursday, October 10, 2013 12:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:53 PM
Points: 320, Visits: 413
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!!!



Thank you!!,

Angelindiego

Post #1503740
Posted Thursday, October 10, 2013 12:50 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 3,939, Visits: 8,938
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 = 'asd f '

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




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1503746
Posted Thursday, October 10, 2013 1:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:53 PM
Points: 320, Visits: 413
Thank you....gives me something to go on!! I will give this a shot!


Thank you!!,

Angelindiego

Post #1503757
Posted Thursday, October 10, 2013 1:26 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:53 PM
Points: 320, Visits: 413
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.


Thank you!!,

Angelindiego

Post #1503765
Posted Thursday, October 10, 2013 1:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 3,939, Visits: 8,938
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.


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1503770
Posted Thursday, October 10, 2013 6:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:53 AM
Points: 3,422, Visits: 5,368
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.

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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1503839
Posted Tuesday, October 15, 2013 4:14 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:17 PM
Points: 2,268, Visits: 3,428
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1505016
Posted Tuesday, October 15, 2013 6:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1505030
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse