Truncate text in table? (ie 101928494 will become 928494)

Lets say I have a column with the following text in 5 rows:

1015758349
10147594734
10173
101288
1019384048

I'd like to run a MSSQL command (if possible) to take out the '101'
from every single row. Is there a command for this?

Thanks

re: Truncate text in table? (ie 101928494 will become 928494)

Am Tue, 19 Aug 2008 04:18:49 -0700 (PDT) schrieb aenriquez@gmail.com:
if it is datatype varchar just do a:
update table set field = substring(field,4,99)

bye,
Helmut

re: Truncate text in table? (ie 101928494 will become 928494)

On Tue, 19 Aug 2008 04:18:49 -0700 (PDT), aenriquez@gmail.com wrote:
If you always want the first three characters removed you could use
SUBSTRING and specify 4 as the start column.

If you want to actually update the table and change those rows, you
might look at something like:

UPDATE Whatever
SET Something = SUBSTRING(Something,4,100)
WHERE Something IN
('1015758349', '10147594734', '10173', '101288', '1019384048')

Roy Harvey
Beacon Falls, CT

re: Truncate text in table? (ie 101928494 will become 928494)

On Aug 19, 2:00*pm, "Roy Harvey (SQL Server MVP)"
<roy_har...@snet.netwrote:Yes, I want to update the table ideally without changing the string
length for any of the rows. Maybe a REPLACE will work better.

re: Truncate text in table? (ie 101928494 will become 928494)

Another method:

UPDATE Foo
SET col = STUFF(col, 1, 3, '')
WHERE col LIKE '101%';


Plamen Ratchev
http://www.SQLStudio.com

re: Truncate text in table? (ie 101928494 will become 928494)

(aenriquez@gmail.com) writes:You can use substring(col, 4, len(col)) to overcome that issue.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

re: Truncate text in table? (ie 101928494 will become 928494)

On Aug 19, 11:06*pm, Erland Sommarskog <esq...@sommarskog.sewrote:Thanks all. This query worked perfectly for me:
UPDATE tablename
Set field = SUBSTRING(field,3,len(field))
WHERE (field like '10%')