Solved: Truncating the number of characters in a column

1

Hi,

I've got a column in SQL which has data in it that needs trunctating to 16 characters where some of it is allowed to go to 20 characters. I've had a look everywhere and I cannot find anywhere that tells me how to do an update statement to truncate the data to 16 characters. I've got a where clause that allows me to find any data that is over 16 characters but there is usually a lot of it and rather than having to manually remove the last 4 characters I was hoping that I'd be able to write an update statement that would do it for me.

The where clause is: where len([column]) > 16

I would really appreciate any help in sorting this out, thank you in advance :slight_smile:

2

Update tgt
Set tgt.thatcolumn = left(tgt.thatcolumn,16)
From thetable tgt
Where len(tgt.thatcolumn) >16

Test first in non prod

2 Likes
3

That's awesome! Thank you so much! Out of curiousity what does the tgt refer to?

4

An alias to

From thetable tgt.
You can also do this

UPDATE table_name
SET column = left(column,16)
WHERE len(column) > 16

1 Like
5

Oh yeah, I just realised now it's pointed out that it's an alias, should have noticed that really :laughing: Thanks :slight_smile:

6

This will be problematic if column has empty space padding on left side

Select '              oops'
7

The likelihood of any empty space padding in this field is so low that it's worth it. I've not encountered any instance of this field having empty space padding before to be honest. Although that is something good to know, thanks :slight_smile:

8

I have to ask... why is this being allowed? It seems to be wanton destruction of data but I don't know the particulars. What is ever in the last 4 bytes that are being truncated and why don't they matter? Not a criticism here... I'm just insanely curious about this.

9

It's the fob for a clocking in machine but our customers have a habit of double scanning them and the characters in the fobs are 16 whilst the field has a character limit of 20. We're migrating them to a newer version of the software which has the character limit set to 16 but it fails if they have more than 16 characters in the fob field on their records. So instead of manually removing them I wanted something that would cut off any characters over the 16 characters

1 Like
10

Got it. Thanks for taking the time to explain, Matt. Much appreciated.