how to sort a sentence to remove the first 3 character

Question

Post reply

how to sort a sentence to remove the first 3 character

midan1

SSCertifiable

Points: 5100
More actions
March 24, 2004 at 9:16 am

#160663

how can i do this
remove the 3 character from the beginning
like this
123456789
i wont to see only this
456789

thnks ilan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

You could use SUBSTRING and LEN:

DECLARE @mystring VARCHAR(10)

SET @mystring = '123456789'

SELECT SUBSTRING(@mystring, 4, (LEN(@mystring))

LEN finds the length of the string. Then SUBSTRING extracts the portion of the string starting at the 4th character and going for the length of the string.

-SQLBill

GregLyon SSCommitted Points: 1946 More actions · Answer 2

You can also use the RIGHT function with LEN for pretty much the same effect.

DECLARE @mystring VARCHAR(10)

SET @mystring = '123456789'

SELECT RIGHT(@mystring, LEN(@mystring) - 3)

Of course you can substitute a column name for @mystring and it can be one of the items in the SELECT list of a typical SELECT/INSERT/UPDATE query.

midan1 SSCertifiable Points: 5100 More actions · Answer 3

How to create function

and use it in my table ?

OR

how can i do it in table

if i have 500000 records

thnks

ilan

GregLyon SSCommitted Points: 1946 More actions · Answer 4

if you want to do an UPDATE (overwrite) query it would look like this:

UPDATE t SET MyField = RIGHT(@MyField , LEN( MyField ) - 3)
FROM MyTable t

Note that this query will overwrite EVERY ROW of the table MyTable, column MyField with the new data...no way to 'undo'.

You could add a new field called 'MyNewField' and modify the above statement like this (run from query analyzer):

ALTER TABLE MyTable ADD MyNewField Varchar(256)
GO
UPDATE t SET MyNewField = RIGHT(@MyField , LEN( MyField ) - 3)
FROM MyTable t

You could also have a view based on the table which would not change any of the base table or data. the SQL for the view would be something like:

SELECT *, RIGHT(@MyField , LEN( MyField ) - 3) As NewCol
FROM MyTable t

I guess it all depends on what you're trying to accomplish. If it's data cleanup, the first choice probably makes sense. If it's just looking at data the last one might make the most sense.