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

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

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

Roy Harvey
Beacon Falls, CT

On Aug 19, 2:00*pm, "Roy Harvey (SQL Server MVP)"
<roy_har...@snet.net> wrote:
> 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(Something,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.

Another method:

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


Plamen Ratchev
http://www.SQLStudio.com

(aenriquez@gmail.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@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

On Aug 19, 11:06*pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> *(aenriq...@gmail.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...@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

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