Triming information in a column

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

Triming information in a column

Author

Topic

budman
Starting Member

2 Posts

Posted - 2011-11-11 : 14:20:20

Hi,
I have a database that I need to clean up the data and I am new to SQL queries.

This is my problem I have a column in the database I need to remove just parts of the information and leave the rest of the information.

This is the column of data. I need to remove the manufactured name but leave the rest of the information. I have over 330k rows to do this to and I do not want to manually do it.

what I have bolded is what I want to remove.
OfficeMax Dry Erase Marker (Red)
OfficeMax Dry Erase Marker (Blue)
Verbatim CD-R
Brother Tape Cutter TC8 for PT6

Thanks for any help.
John

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-11-11 : 14:51:50

Is the manufactured name always the first word followed by a space? If so, you can simplify this example, but heres a start:


declare @yourTable table (s varchar(50));
insert into @yourTable
	select 'OfficeMax Dry Erase Marker (Red)' union all
	select 'OfficeMax Dry Erase Marker (Blue)' union all
	select 'Verbatim CD-R' union all
	select 'Brother Tape Cutter TC8 for PT6' union all
	select 'Dont alter this row'

select	[new] = case
			when substring(s, 0, charindex(' ', s)) in ('OfficeMax', 'Verbatim', 'Brother')
			then stuff(s, 1, charindex(' ', s), '')
			else s 
		end,
	[orig] = s
from	@yourTable yt

Something like this should work

Nathan Skerl

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-12 : 05:17:26

i would suggest checking your table and confirming if all the data are coming in consistent format (manufactured name first followed by space and then other parts ) before applying script. In case you're more than one format you've apply seperate scripts over each subset to make sure changes are correctly applied to each group of them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

budman
Starting Member

2 Posts

Posted - 2011-11-15 : 09:53:54

Thanks, but not all the rows have a manufacture name in it and the spaces are difference with the same manufacture. The queue works, but i would need to know all the rows information to run it and then replace it back in to the original row.

Sorry I am just learning about T-SQL command and still not up to understanding it completely I had to research all that you have wrote just to understand what it was doing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources