August 1, 2008 at 6:58 am
A bit confused, the RTRIM() OR LTRIM() functions do not seem to work any more. We do have a straight forward SQL 2K (SP4). This has started very recentlty. The UPDATE query is quite simple....
update
set )
Any advice please?
Regards
August 1, 2008 at 7:02 am
A bit hard to tell , they still do work, are you able to post your query and the table with some sample data?
August 3, 2008 at 8:52 pm
Question: are you using CHAR or VARCHAR? I ask because RTRIM and LTRIM probably do not work the way you expect for CHAR. Remember, they're not variable in length, if you trim them, they just have to be re-filled again.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 3, 2008 at 9:10 pm
I agree with Barry... if you are using CHAR instead of VARCHAR, then things don't appear quite the same way. Also, there's a lot of characters that look like spaces that aren't. For example, I believe character 160 is a hard-space and is unaffected by Trim functions.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2008 at 3:44 am
Thanks all for your time and advice. Apology, I am a bit late in getting back. Was out for a few days. Unfortunately the datatey is varchar in both the tables. As I was checking manually I found a few 'New Line' charecter in a few rows (and the TRIM worked after those were removed), However, could not see any duch charecter in the reming ones, and the problem still continues......
The DML is pretty straight forward..
UPDATE Table_Name
SET Col1 = RTRIM(LTRIM(Col1))
WHERE Condition
Thanks and Regards
August 4, 2008 at 4:01 am
I think you should identify one of the rows you want to update and then print out the ascii code of each character in the field: maybe you'll find tabs (char(9)) and other messy things.
You could try:
UPDATE Table_Name
SET Col1 = REPLACE(RTRIM(LTRIM(Col1)),char(9),'')
WHERE Condition
Regards
Gianluca
-- Gianluca Sartori
August 4, 2008 at 4:20 am
hope this will help;
http://sqlservercodebook.blogspot.com/2008/03/sql-string-functions-tutorial.html
August 4, 2008 at 6:25 am
Unless I am mis reading something (I haven't had my coffee yet), the article http://sqlservercodebook.blogspot.com/2008/03/sql-string-functions-tutorial.html gives the same definition of LTrim and RTrim. I believe that the definition for RTrim is incorrect.
August 5, 2008 at 8:38 am
I did make a procedure to remove bad charachters a long time ago.
In this case the characters to be removed are stored in table scrapsign with column sign varchar(1).
This will probably also work if you store the ascii code per sign to be removed in each row (extend varchar to more signs)
DECLARE @sign varchar(1)
DECLARE sign_Cursor CURSOR
FOR SELECT sign
FROM scrapsign
OPEN sign_Cursor
FETCH NEXT
FROM sign_Cursor into @sign
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Table_Name
SET Col1 = REPLACE(RTRIM(LTRIM(Col1)),@sign,'')
--WHERE Condition
FETCH NEXT
FROM sign_Cursor into @sign
END
CLOSE sign_Cursor
DEALLOCATE sign_Cursor
//Gosta
August 5, 2008 at 8:41 pm
Or... you can beat the crud out of a string anyway you like... 🙂 CREATE FUNCTION dbo.udf_Extract
/*******************************************************************************
Purpose:
========
Removes unwanted characters from a string.
Notes:
======
Possible inclusive values for @Type...
Alpha or Ltr = Letters [A-Z,a-z] included
Numeric or Num = Numeric digits [0-9] included
Spaces or Spc = Spaces included
Symbols or Sym = Special symbols not in the above but less than ASCII
127 and greater than ASCII 31 included (sometimes
called "special characters"). These include
characters like `~!@#$%^&*()-_=+\|[{]};:'", /?, etc.
Extended or Ext = Characters above ASCII 127 are included
Control, Ctrl, or Ctl = Control characters such as TAB, Carriage Return, etc.
Trim or Trm = Leading and trailing spaces are dropped.
Condense,Cond, or Cnd = Multiple adjacent spaces reduced to 1 space.
Any combination of the above will work. For example, LtrNumSpcTrm will return
all letters, numbers, and embedded spaces and without any leading or trailing
spaces.
Created by Jeff Moden, 02/26/2006
*******************************************************************************/
--===== Declare the input parameters and the return type of the function
(
@String VARCHAR(8000), --Contains the input string to "clean"
@Type VARCHAR(100) --Contains controls of what to return
)
RETURNS VARCHAR(8000)
AS
BEGIN
--==============================================================================
--===== Define the local variables
DECLARE @Desired TINYINT --@Type converted to a bit mask
DECLARE @Output VARCHAR(8000) --The cleaned output
DECLARE @iLength INT --Length of the input string
DECLARE @NumCnd INT --Times to convert 2 spaces to 1 for condensation
--===== Preset the output to an empty string
SET @Output = ''
--===== Determine the length of the input string
-- Use DATALENGTH instead of LEN to handle @String of all spaces
SET @iLength = DATALENGTH(@String)
--===== Convert the input control to a bit mask to make processing easier
SET @Desired = 0
IF @Type LIKE '%Alpha%' OR @Type LIKE '%Ltr%'
SET @Desired = @Desired + 1
IF @Type LIKE '%Numeric%' OR @Type LIKE '%Num%'
SET @Desired = @Desired + 2
IF @Type LIKE '%Spaces%' OR @Type LIKE '%Spc%'
SET @Desired = @Desired + 4
IF @Type LIKE '%Symbols%' OR @Type LIKE '%Sym%'
SET @Desired = @Desired + 8
IF @Type LIKE '%Extended%' OR @Type LIKE '%Ext%'
SET @Desired = @Desired + 16
IF @Type LIKE '%Control%' OR @Type LIKE '%Ctl%' OR @Type LIKE '%Ctrl%'
SET @Desired=@Desired + 32
--===== Create the output using only those types of characters desired
SELECT @Output = @Output + SUBSTRING(@String,N,1)
FROM dbo.Tally
WHERE N <= @iLength
AND @Desired&CASE --Character type must be included in desired mask
-- Mask character as '1' if it is Alpha (A-Z,a-z)
WHEN SUBSTRING(@String,N,1) LIKE '[A-Z]'
AND ASCII(SUBSTRING(@String,N,1)) < 127
THEN 1
-- Mask character as '2' if it is Numeric (0-9)
WHEN SUBSTRING(@String,N,1) LIKE '[0-9]'
THEN 2
-- Mask character as '4' if it is a space
WHEN SUBSTRING(@String,N,1) = ' '
THEN 4
-- Mask character as '8' if it is a Special Symbol
WHEN SUBSTRING(@String,N,1) LIKE '[!-~]'
THEN 8
-- Mask character as '16' if it is an Extended Character
WHEN SUBSTRING(@String,N,1) > CHAR(127)
THEN 16
-- Mask character as '16' if it is a Control Character
WHEN SUBSTRING(@String,N,1) < ' '
OR SUBSTRING(@String,N,1) = CHAR(127)
THEN 32
--Should never occur but good practice.
ELSE 0
END > 0
--===== If the trim option is present, drop leading and training spaces
IF @Type LIKE '%Trim%' OR @Type LIKE '%Trm%'
SET @Output = LTRIM(RTRIM(@Output))
--===== If the condense option is present, reduce multiple spaces to one.
IF @Type LIKE '%Condense%' OR @Type LIKE '%Cnd%' OR @Type LIKE '%Cond%'
BEGIN
-- Determine Number of times we need to convert 2 spaces to 1
-- This just helps performance a bit instead of always doing 13
SET @NumCnd = CAST((LOG(LEN(@Output))/LOG(2))+1 AS INT)
-- Now, condense the spaces using a setbased "loop"
SELECT @Output = REPLACE(@Output,' ',' ')
FROM dbo.Tally
WHERE N<@NumCnd
END
--==============================================================================
RETURN @Output
END
GO
--===== Small demo... have some fun....
SELECT dbo.udf_extract('(248) 515-1212','Num')
FROM dbo.TALLY
If you don't have a Tally (or Numbers) table or, maybe, don't know how it works even if you do have one, take a look at the following URL. 😉
http://www.sqlservercentral.com/articles/TSQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply