Trim Character - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trim Character

Author

Topic

sel129
Starting Member

9 Posts

Posted - 2007-07-05 : 03:48:13

OK, the problem is like this; say the table is sumthing like this

i have a table 't3' has 3 fields
id(number,2)
date(date/time)
text(varchar,200)

eg. id date text
-----------------------
01 05-jul-07 abcdefghijkl (i want to get rid of the first 6 characters i.e. abcdef)

i want to display 'text' field from t3 table but with the first 6 characters trimmed/removed (i want to get rid of the 6 first characters)

so i did:

select LTRIM(text,6) from t3

but the above code didn't work because LTRIM only trims spaces NOT characters... Is there any way that i can trim off the first 6 characters from every record in the 'text' field?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-05 : 03:50:49

You need to use SUBSTRING() function.

Another option is to use RIGHT() function:

Select Right(Col, datalength(Col) - 6)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-05 : 03:50:57

use substring(), or right() function to do it.

KH
[spoiler]Time is always against us[/spoiler]

sel129
Starting Member

9 Posts

Posted - 2007-07-05 : 04:01:26

i did: select right(text, datalength(text) - 6) from t3

i get "invalid colunm name" error on the word right

Harsh,
What you mean by 'Another option is to use SUBSTRING() function'?

khtan,
can you elaborate more on that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-05 : 04:18:33

maybe he mean this

quote:
'Another option is not to use SUBSTRING() function'?

KH
[spoiler]Time is always against us[/spoiler]

sel129
Starting Member

9 Posts

Posted - 2007-07-05 : 04:18:38

OK, nvm, i got it already

select substr(text,6) from t3

thx guys

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-05 : 04:22:46

[code]CREATE TABLE #t3
(
[id] decimal(2),
[date] datetime,
[text] varchar(200)
)

INSERT INTO #t3
SELECT 01, '05-jul-07', 'abcdefghijkl'

SELECT [original text] = [text],
[method 1] = RIGHT([text], LEN([text]) - 6),
[method 2] = SUBSTRING([text], 7, LEN([text]) - 6),
[method 3] = STUFF([text], 1, 6, '')
FROM #t3

DROP TABLE #t3
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-05 : 04:23:46

quote:
Originally posted by sel129

OK, nvm, i got it already

select substr(text,6) from t3

thx guys

substr() ? What version of SQL Server are you using ?

KH
[spoiler]Time is always against us[/spoiler]

sel129
Starting Member

9 Posts

Posted - 2007-07-05 : 04:53:14

i actually dunno what version i'm using, probably an old one.. hehe

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-05 : 04:56:23

try print @@version

KH
[spoiler]Time is always against us[/spoiler]

sel129
Starting Member

9 Posts

Posted - 2007-07-05 : 05:03:52

hhmm... it doesn't work '@@version:not found'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-05 : 05:07:51

OK. Are you using Microsoft SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-05 : 09:38:14

quote:
Originally posted by khtan

quote:
Originally posted by sel129

OK, nvm, i got it already

select substr(text,6) from t3

thx guys

substr() ? What version of SQL Server are you using ?

KH
[spoiler]Time is always against us[/spoiler]

Substr is used in ORACLE and other front end languages

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources