** How to trim the first 3 characters of a String (in SQL Server 2000)

Hello experts,
I am using SQL Server 2000 Query Analyzer so the TRIM function isn't an option.

How can I trim the first 3 characters of a string?

Who is Participating?
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
I think the inverse is requested:

select substring('123456',4,len('123456'))
define "trim"

are you talkinga bout this?

declare @x = '123456'
select substring(@x, 3, 3)
dunkin1969Author Commented:
SUBSTRING will work.  However, the remainder of my string can be various lengths.  So if I use:

select substring (myfield, 3, 4)

it will return just 4 charaters.  My strings vary in length from 2 characters to 50.  Is there a way to ignore the 2nd argument?

No big deal, I can always use 50 as the max:

 select substring (myfield, 3, 50)

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Kyle AbrahamsSenior .Net DeveloperCommented:
dunkin: use len(myfield) as the last parameter.
chapmandewConnect With a Mentor Commented:
right, you can use anything you want there...I was just giving you an example.  you can do this:

select substring (myfield, 3, 8000)

and it shoudl work just fine.
dunkin1969Author Commented:
Thanks experts !!!
You can also use STUFF (http://msdn.microsoft.com/en-us/library/ms188043.aspx)
It's a very handy string function.

I just thought I would add it as an additional option which can add some versatility. I have run some performance tests (basic CPU and execution time population a million rows in a table variable using STUFF, SUBSTRING(LEN()) and SUNBSTRING(8000).

It's a slow desktop box though and though these are averages over runs 2-4 of each query (to allow plan caching) The results bounced a bit from one to the other but were so close that unless this is for a query that is going to be run on millions of rows all the time the timings are very similar.
Literal length does seem to have the slight edge more often though.

SQL Server Execution Times: STUFF('monkeys are great!',1,3,'')
   CPU time = 3180 ms,  elapsed time = 13022 ms.
(1000000 row(s) affected)

SQL Server Execution Times: SUBSTRING('monkeys are great!',3,LEN('monkeys are great!')-3)
   CPU time = 3171 ms,  elapsed time = 13034 ms.
(1000000 row(s) affected)

SQL Server Execution Times: SUBSTRING('monkeys are great!',3,8000)
   CPU time = 3125 ms,  elapsed time = 12974 ms.
(1000000 row(s) affected)
SET @mystring = 'monkeys are great!'


Open in new window

dunkin1969Author Commented:
Very good information.  I've never heard of STUFF before.  Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.