September 9, 2010 at 2:01 pm
I'm new to SQL and add to that we use MS SQL 2000.
As hinted in the subject, I need to know if and how to search a column or string from the right to the left starting at a specific character position in the column or string, looking for the first blank character, then creating a new string with all the characters from the blank to the end of the searched column or string. I have tried using SUBSTRING but it splits some words.
Thanks for your help.
September 9, 2010 at 2:15 pm
You're looking for the REVERSE() function, paired with a CHARINDEX() to find the location of the space.
This can be processor intensive, hopefully this isn't a regularly running proc and is only needed at load times or special occassions.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 9, 2010 at 2:47 pm
Does not a
substring( @mystring, charindex( @mystring, ' ') + 1, len(@mystring)
work?
September 10, 2010 at 9:35 am
I'm working with the example , no success yet.
This is what I need to do.
There is a column that is 60 characters long. I need to create two 30 character columns. Dong this I do not want split any words but split at the first blank to the left of position 30.
sample data
column1 source
6175 Spring Mountain Road Suite 2-B
1141 Bacons Bridge Road
4801 East Independence Boulevard
621 Edgefield Road
181 East Evans Street, Suite 410
32 Office Park Road 200 Courtyard Bldg
wanted results
column1 source
6175 Spring Mountain Road
1141 Bacons Bridge Road
4801 East Independence
621 Edgefield Road
181 East Evans Street
32 Office Park Road
column2 new
Suitwe 2-B
Boulevard
Suite 410
200 Courtyard Bldg
September 10, 2010 at 10:29 am
2 things.
If you could set that table data up (the source) as a temp table insert that'd be great.
Your results look the exact same visually as the source, not sure what difference you wanted there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 11, 2010 at 5:07 am
bhoverman (9/10/2010)
I'm working with the example , no success yet.This is what I need to do.
There is a column that is 60 characters long. I need to create two 30 character columns. Dong this I do not want split any words but split at the first blank to the left of position 30.
sample data
column1 source
6175 Spring Mountain Road Suite 2-B
1141 Bacons Bridge Road
4801 East Independence Boulevard
621 Edgefield Road
181 East Evans Street, Suite 410
32 Office Park Road 200 Courtyard Bldg
wanted results
column1 source
6175 Spring Mountain Road
1141 Bacons Bridge Road
4801 East Independence
621 Edgefield Road
181 East Evans Street
32 Office Park Road
column2 new
Suitwe 2-B
Boulevard
Suite 410
200 Courtyard Bldg
Applying your logic, 32 Office Park Road 200 Courtyard Bldg will not break up into 32 Office Park Road and 200 Courtyard Bldg. It will break into 32 Office Park Road 200 and Courtyard Bldg. You also have the same issue with 181 East Evans Street, Suite 410
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply