trim/replace specific characters

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

SQL Server 2005 Forums

Transact-SQL (2005)

trim/replace specific characters

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-01-24 : 11:55:41

How would I remove the 'H ' in the following string 'H 159'? See query below..

select holding_company_group_id from holding_company
where holding_company_group_id like 'H%'
order by holding_company_group_id asc

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-24 : 12:10:40

Do your records always have the pattern of "H XXX" ? IF so, then you can use SUBSTRING or LEFT or RIGHT or pretty much any of the string functions. All you need to know is the pattern.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

raaj
Posting Yak Master

129 Posts

Posted - 2008-01-24 : 12:12:31

i am not sure whether u r looking for this one only....try this...
CREATE TABLE ABC (BOOKID VARCHAR (20))
INSERT INTO ABC VALUES ('H 120')
INSERT INTO ABC VALUES ('R 320')
INSERT INTO ABC VALUES ('F 560')
INSERT INTO ABC VALUES ('H 120')
INSERT INTO ABC VALUES ('H 120')

UPDATE ABC
SET BOOKID = RIGHT(BOOKID,3)
WHERE BOOKID LIKE 'H%'

SELECT * FROM ABC

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-01-24 : 12:32:52

dinakar, yes... where xxx can be any number of characters.
Basically, 'H ' would be a constant. I want to strip these characters out of my select statment so that my results look like:

1
11
111
1111
11111
etc.....

Right now I am using replace but would like to see another example.

select productioned.dbo.holding_company.holding_company_name,
replace(productioned.dbo.holding_company.holding_company_group_id, 'H ', '') as group_id
from productioned.dbo.holding_company
where productioned.dbo.holding_company.holding_company_group_id like 'H%'
order by group_id asc

raaj
Posting Yak Master

129 Posts

Posted - 2008-01-24 : 13:58:32

if ur 'H' would be constant then i guess there is no need to use where....like 'H%' in ur query

and after H if u dont have fixed number of characters..u can try using SUBSTRING in this way also:
select productioned.dbo.holding_company.holding_company_name,
substring(productioned.dbo.holding_company.holding_company_group_id, '3', '10') as group_id
from productioned.dbo.holding_company

and when ur using replace, if u want to remove space on the leftside,then u can use LTRIM also in this way :
select productioned.dbo.holding_company.holding_company_name,
LTRIM(replace(productioned.dbo.holding_company.holding_company_group_id, 'H ', '')) as group_id
from productioned.dbo.holding_company

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources