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

[aenriquez@gmail.com]

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

[Helmut Woess]

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

Am Tue, 19 Aug 2008 04:18:49 -0700 (PDT) schrieb aenriquez@gmail .com:

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

if it is datatype varchar just do a:
update table set field = substring(field ,4,99)

bye,
Helmut

[Roy Harvey (SQL Server MVP)]

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

On Tue, 19 Aug 2008 04:18:49 -0700 (PDT), aenriquez@gmail .com wrote:

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

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(Somet hing,4,100)
WHERE Something IN
('1015758349', '10147594734', '10173', '101288', '1019384048')

Roy Harvey
Beacon Falls, CT

[aenriquez@gmail.com]

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

On Aug 19, 2:00 pm, "Roy Harvey (SQL Server MVP)"
<roy_har...@sne t.netwrote:

On Tue, 19 Aug 2008 04:18:49 -0700 (PDT), aenriq...@gmail .com wrote:

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?

>
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(Somet hing,4,100)
WHERE Something IN
     ('1015758349', '10147594734', '10173', '101288', '1019384048')
>
Roy Harvey
Beacon Falls, CT

Yes, I want to update the table ideally without changing the string
length for any of the rows. Maybe a REPLACE will work better.

[Plamen Ratchev]

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

[Erland Sommarskog]

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

(aenriquez@gmai l.com) writes:

Yes, I want to update the table ideally without changing the string
length for any of the rows. Maybe a REPLACE will work better.

You can use substring(col, 4, len(col)) to overcome that issue.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[aenriquez@gmail.com]

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

On Aug 19, 11:06 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

 (aenriq...@gmai l.com) writes:

Yes, I want to update the table ideally without changing the string
length for any of the rows. Maybe a REPLACE will work better.

>
You can use substring(col, 4, len(col)) to overcome that issue.
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.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

Thanks all. This query worked perfectly for me:
UPDATE tablename
Set field = SUBSTRING(field ,3,len(field))
WHERE (field like '10%')