Help with Trim

Question

Post reply

Help with Trim

Viewing 10 posts - 16 through 24 (of 24 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

DBA (5/21/2008)
would the RTRIM function also get rid of the whitespaces like tab?

No. From BOL:

RTRIM - Returns a character string after truncating all trailing blanks.

Tabs, etc... are not blanks, spaces are.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

DBA-640728 SSChampion Points: 12898 More actions · Answer 2

so say I have this in my table

'ComputerAA1'

and someone tries to insert this

'ComputerAA1SPACE' --they will get a duplicate error for the constraint

but if they try to insert this 'ComputerAA1TAB', they would'nt?

my last question

when do the linefeed and the carriage return happen?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

DBA (5/21/2008)
so say I have this in my table
'ComputerAA1'
and someone tries to insert this
'ComputerAA1SPACE' --they will get a duplicate error for the constraint
but if they try to insert this 'ComputerAA1TAB', they would'nt?

Correct. You can verify this with:

[font="Courier New"]CREATE TABLE #test

(

test_type VARCHAR(20),

test_name VARCHAR(10)

)

CREATE UNIQUE INDEX UX_test ON #test(test_name)

GO

INSERT INTO #test

SELECT

'No trailing space',

'Computer1' -- no space

GO

-- this should fail

INSERT INTO #test

SELECT

'trailing space',

'Computer1' + SPACE(1) -- 1 space

IF @@ERROR <> 0

BEGIN

SELECT 'Trailing space insert failed'

END

GO

INSERT INTO #test

SELECT

'trailing tab',

'Computer1' + CHAR(10) -- trailing tab

GO

SELECT *

FROM #test

DROP TABLE #test[/font]

The 😉 is a closing parentheses the forum code changes it to a smiley.

DBA (5/21/2008)
my last question
when do the linefeed and the carriage return happen?

Line feed and carriage return are inserted when you have a textbox that accepts an "ENTER".

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

DBA-640728 SSChampion Points: 12898 More actions · Answer 4

Thank you so much! you have no idea how much i apreciate it. 😀

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Nice job, Jack. This could serve as a textbook example of how to provide technical support.

[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]

rroque SSC Veteran Points: 295 More actions · Answer 6

If you're allowing nullo values in the field and you don't want to display null values do this.

select fld1,isnull(fld2,'')

or

select fld1,isnull(fld2,'Something')

rroque SSC Veteran Points: 295 More actions · Answer 7

If you're allowing null values but don't want to display a null value to your customers, do this.

select isNull(fld1,'')

or

select isNull(fld1,'N/A')

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

rbarryyoung (5/22/2008)
Nice job, Jack. This could serve as a textbook example of how to provide technical support.

Thanks for the nice words. I found this interesting because I had always assumed that SQL Server trimmed trailing whitespace when inserting/updateing varchar/nvarchar columns and when using the trim functions. Now I learned that it will not trim on insert/update and that the trim functions only trim blanks(spaces).

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Mauve SSChampion Points: 11316 More actions · Answer 9

I found this interesting because I had always assumed that SQL Server trimmed trailing whitespace when inserting/updateing varchar/nvarchar columns

Trailing blanks ARE significant in varying character columns in all of the major SQL-based RDBMs that I've worked on (IBM DB2, Oracle, SQL Server).

So for a varying length string column:

[font="Courier New"]"A"

"A "

"A "[/font]

are all different values.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

John,

SQL Server used to have ANSI_PADDING set to off, so when inserting 'a', 'a ', 'a ', etc... into varchar/nvarchar the trailing spaces were trimmed so all that was stored in each case was 'a'. Now the ANSI_PADDING setting is on which means the trailing spaces ARE stored, but, if you have a Unique Index/Constraint on that column SQL Server does trim so only 1 of the examples can be successfully inserted. If you check out the links in my first post on this thread you can see a table that lists how SQL Server treats trailing spaces in varchar/nvarchar columns.

I don't know how any of the other RDBMS's treat them, but if you read the links in my first post you can see the testing I did and do it yourself to see the results.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question