Select Into Creates HUGE Varchar????? But Data doesn't reflect?? 1

[kjv1611]

I'm hoping there's just something I'm overlooking..

I'm using RTRIM and LTRIM to make sure no extra spaces in the original text make its way into the final table.

So my query (the part creating the huge field) is:

SELECT LTRIM(RTRIM(REPLACE(a.[BusinessName],'OldName','NewName'))) as BusinessName
FROM MyTable a

And when I pull the data, the longest individual value in a given record in this field is 30 characters long.

So why in the world is SQL (when using SELECT INTO) slapping a varchar(8000) on the field???

What brought me to this to begin with was I was creating the table, and inserting into it. But it kept giving me the "data will be truncated" message and quitting. So when I used SELECT INTO, I got a varchar of 8000 characters for the size.. though not a single value fits that size????

Any thoughts? Is it b/c of the original value?

I looked at the table the value is coming from, and it's a varchar(255), b/c was imported from a text file, I think.

Thanks for any thoughts or suggestions.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[gmmastros]

It's the replace function that is causing the length to be 8000. Look at it this way.... suppose you had a varchar(255) and you did a replace like this:

Replace(YourColumn, 'a', 'bbbbbbbbbbbbbbb')

Now suppose you had 255 a's in the data. each a would be replaced by a bunch of b's causing the resulting string to be larger than the original. This is why replace returns a varchar(8000).

You can correct this by 'sizing' the data after the replace function, like this:

SELECT LTRIM(RTRIM([!]Convert(VarChar(255), [/!]REPLACE(a.[BusinessName],'OldName','NewName')[!])[/!])) as BusinessName

This will now return a varchar(255) instead of varchar(8000).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kjv1611]

Ah! Thanks! I'll tinker with that! Thanks a bunch! Believe it or not, I actually thought about using Convert or Cast in there, but thought - Naa, that wouldn't make sense.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Alright! It worked! I wound up putting in 2 CONVERT statements, b/c it was still adding some space in the RTRIM and LTRIM statements.. So anyway.. I threw in the extra CONVERT, and it worked! Forced it to stick to 30 characters, and since this particular field (thankfully) has a very small number of possible values, I was able to select that field, grouped, to verify nothing was cut-off.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

And really, I suppose I could have just put in one CONVERT function rather than twice.. just on the outside, but it's working with 2 for sure.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[gmmastros]

There's another way of doing this. Performance is probably about the same, but I think the code is a bit clearer.

SELECT LTRIM(RTRIM([!]Left([/!]REPLACE(a.[BusinessName],'OldName','NewName')[!], 255)[/!])) as BusinessName

When you use the left function, it converts the data type for you. I think this is slightly clearer because it's more obvious that you are getting the left most 255 characters. Convert was doing that too, but it is (to me) a little less clear.

By the way, I was testing this by using the SQL_VARIANT_PROPERTY function, like this:

Select SQL_VARIANT_PROPERTY(Left(Replace('aaaaaaa', 'a', 'bbbbbbbbb'), 5), 'MaxLength')

Select SQL_VARIANT_PROPERTY(Convert(VarChar(5), Replace('aaaaaaa', 'a', 'bbbbbbbbb')), 'MaxLength')

In this case, it returns the defined size of the varchar data. Remove the replace and convert and you will see that the replace function returns maxlength = 8000.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kjv1611]

Thanks for that extra bit. I'll have to play around with that function/routine to check things out when I run into a wall again.. and I'll play around with what differences I get for different scenarios for the current issues.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[robertfah]

Got a ? for you guys....I think I understand the issue and the resolution but using GMM's example of replacing 'a' with 'bbbbbbbbb', if you specified the size :

SELECT LTRIM(RTRIM(Convert(VarChar(255), REPLACE(a.[BusinessName],'OldName','NewName')))) as BusinessName

wouldn't it truncate any data over 255? Or does the end user always want to return a mx of 255 characters?

 

[gmmastros]

Let me explain...

First, let's look at the functions involved here.

[tt]
LTRIM(RTRIM(Convert(REPLACE)))
[/tt]

The replace function occurs first, then the convert and finally the RTrim and LTrim. When you replace 'a' with 'bbbbbbbb', replace will return a varchar(8000). Next is the convert function which actually does the truncation.

Does this make sense now?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

Ah...yes, didn't take into account for the TRIM functions....now it makes perfect sense!