Cannot remove blank espace

Question

Post reply

Cannot remove blank espace

luissantos

SSCertifiable

Points: 7391
More actions
January 20, 2012 at 5:03 am

#255210

Hello comunity
I have a table where my field CCT is varchar(20), but on my desktop application some records append blanck space after my string:
ex: 71.9.909------------ (where '-' is black space
If i run this query :
update cu
set cct = rtrim(cct)
from cu WHERE
cct like '72.1.107%'
i try also :
update cu
set cct = rtrim(cct) --replace('72.1.107 ','','72.1.107')
from cu WHERE
cct like '72.1.107%'
both update don´t result. i mean field mantain the black space.
Could someone explain why.
Many thanks
Luis Santos

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

What does the following return?

SELECT ASCII(RIGHT(cct ))

FROM cu

WHERE cct LIKE '72.1.107%'

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

luissantos SSCertifiable Points: 7391 More actions · Answer 2

Hello

Your TSQL return error RIGHT, i run like this

SELECT ASCII(RIGHT(CCT,20))

from cu

WHERE cct like '72.1.107%'

QA return : 55

I solve my problem using this UPDATE:

update cu

set cct = substring(cct,1,8)

from cu WHERE

len(cct) > 8 and cct like '73.1%'

Any explanation ?

Best regards,

Luis Santos

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

Based on what you explained, all of them should work. Can you please provide us DDL & consumable data to explore your issue? I am sure it’s data driven issue.

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

luissantos (1/20/2012)
Any explanation ?

Yes, the white space trailing your varchar wasn't a space - it was a tab or some other white space character instead.

e.g.

DECLARE @test-2 TABLE(testID INT IDENTITY, cct VARCHAR(20));

INSERT INTO @test-2

SELECT ' 71.9.909 ';

SELECT *, LEN(cct) FROM @test-2;

WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),

t2 AS (SELECT 1 N FROM t1 x, t1 y),

t3 AS (SELECT 1 N FROM t2 x, t2 y),

t4 AS (SELECT 1 N FROM t3 x, t3 y),

tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N

FROM t4 x, t4 y),

Cleaner AS (SELECT testID, (SELECT CASE WHEN SUBSTRING(cct, N, 1) NOT LIKE '[^0-9]' OR SUBSTRING(cct, N, 1) = '.'

THEN SUBSTRING(cct, N, 1)

ELSE '' END + ''

FROM tally

WHERE N <= LEN(cct)

FOR XML PATH('')) AS clean

FROM @test-2)

UPDATE b SET cct = a.clean

FROM Cleaner a

INNER JOIN @test-2 b ON a.testID = b.testID;

SELECT *, LEN(cct) FROM @test-2;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

luissantos (1/20/2012)
Hello
Your TSQL return error RIGHT, i run like this
SELECT ASCII(RIGHT(CCT,20))
from cu
WHERE cct like '72.1.107%'
QA return : 55
I solve my problem using this UPDATE:
update cu
set cct = substring(cct,1,8)
from cu WHERE
len(cct) > 8 and cct like '73.1%'
Any explanation ?
Best regards,
Luis Santos

Apologies... I left out the 2nd operand of RIGHT. It should have been...

SELECT ASCII(RIGHT(cct,1))