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.
| Author |
Topic |
|
Brittney10
Posting Yak Master
154 Posts |
 Posted - 2011-03-25 : 11:05:38
|
I'm trying to normalize a column of data. I have a field that contains names, however, the names each contain a period, then following the period is a bunch of junk data i do not need. For example:Name.Sometext.SomeMoreText So I need to clean this up and ultimately come up with just:Name I figure the best approach is to use the TRIM function, but i don't know how to tell it to start triming at the first period it encounters. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-03-25 : 11:13:43
|
Do you need to parse the string to get 3 columns (Name, SomeText and SomeMoreText) or just the Name? If jus the name, you cna do something like:DECLARE @S VARCHAR(100) = 'Name.Sometext.SomeMoreText'SELECT SUBSTRING(@S, 1, CHARINDEX('.', @S) - 1) |
 |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-03-25 : 11:16:18
|
| SELECT LEFT(YourColumnName, CHARINDEX('.',YourColumnName, 0) - 1)Hey, it compiles. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-25 : 11:17:08
|
| [code]update YourTable set YourColumn = case when YourColumn like '%.%' then stuff(YourColumn ,charindex('.',YourColumn ),len(YourColumn ),'') else YourColumn end[/code] |
|
|
|
Brittney10
Posting Yak Master
154 Posts |
Posted - 2011-03-25 : 11:44:42
|
quote:
Originally posted by Lamprey
Do you need to parse the string to get 3 columns (Name, SomeText and SomeMoreText) or just the Name? If jus the name, you cna do something like:DECLARE @S VARCHAR(100) = 'Name.Sometext.SomeMoreText'SELECT SUBSTRING(@S, 1, CHARINDEX('.', @S) - 1)
This worked. Thanks! I just had to add a where to it to avoid returning 0 or nullWHERE CHARINDEX('.', @S) > 0 |
|
|
|
|
|
|
|
|
Normalizing Data (RESOLVED)