select first 30 characters before space

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

select first 30 characters before space

Author

Topic

McDoogle
Starting Member

12 Posts

Posted - 2011-03-21 : 13:50:43

select left (itemdescription1, charindex(' ',itemdescription1,30)) as shortened
from inmast

I tried the above first and I'm kind of on the right track. However I need to select the first space BEFORE 30 characters and the above selects the first space AFTER. I do not want to cut any words off. I'm not sure what to do but my boss suggested this.

From Boss:
"That’s a good thought but it finds the FIRST space AFTER 30 chars. I want the LAST space BEFORE 30. Also, I hate looking for ‘ ‘ because it can be hard to tell the difference between ‘ ‘ and ‘’, depending on the font. Use char(32) instead. That’s just to make the code easier, there is no functional difference."

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-21 : 14:10:58

could you give some sample input and what you want the output to be?

Thanks,

Jim

Everyday I learn something that somebody else already knew

McDoogle
Starting Member

12 Posts

Posted - 2011-03-21 : 14:33:47

Ok...

Pretend this is a description.


The dog went out and played with the ball in the yard.
111111111111111111111111111111000000000000000000000000

The 1's count up to 30 characters, the 0's are what goes beyond 30.

I would like the results to return this.
The dog went out and played

As you can see instead of returning part of the word (wi) from the word with, it just returned the closest space or blank character before the 30 count was reached.

edit: added code tags to help align text

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-21 : 14:49:17

[code]declare @s table(s varchar(128))
insert @s values('The dog went out and played with the ball in the yard.')
select reverse(substring(reverse(left(s,30)), charindex(' ',reverse(left(s,30))),len(s)))
from @s[/code]Replace the table and column references with yours.

And tell your boss to change his/her font, or get better glasses.

McDoogle
Starting Member

12 Posts

Posted - 2011-03-21 : 15:03:25

The dog played, is just an example. There are 8,125 rows that need to be shortened to 30 characters while not cutting off words, and each description is different. Not sure if your syntax will work properly because it uses my example.

Thanks thus far though )
And he said there was a way to define a space instead of using ' '. Like Char32 or soemthing.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-21 : 15:10:02

Yahoo! I'm taking a win on this Rob. I came up with the same solution, minus the time I spent second guessing myself.

McDoogle

just replace @s with your actual table name and, s with the actual name of the column.
in Rob's solution/

Jim

Everyday I learn something that somebody else already knew

McDoogle
Starting Member

12 Posts

Posted - 2011-03-21 : 15:32:08

Okay one of us is confused.
Once again, I do not want to use the dog example to declare anything. This is fake, this sentence is not anywhere in the actual table.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-21 : 15:36:18

It's you.

quote:

just replace @s with your actual table name and, s with the actual name of the column.
in Rob's solution

Jim

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-21 : 15:41:01

quote:
Yahoo! I'm taking a win on this Rob. I came up with the same solution, minus the time I spent second guessing myself.

Sweet! Now I know it actually works.

McDoogle
Starting Member

12 Posts

Posted - 2011-03-21 : 15:55:03

Consider this still open if anyone else comes along, that knows how to do this, without using "the dog went out and played with the ball in the yard."

I will post the answer when I finally give in and ask him how to do it, but he wanted me to figure it out myself. And using forums and looking it up still counts as doing it myself.

McDoogle
Starting Member

12 Posts

Posted - 2011-03-21 : 16:06:18

declare inmast table(itemdescription1 varchar(128))
insert inmast values('The dog went out and played with the ball in the yard.')
select reverse(substring(reverse(left(s,30)), charindex(' ',reverse(left(itemdescription1,30))),len(itemdescription1)))
from inmast

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'table'.

Error I recieved. I really don't want to alter any of the current content, just get a result set. This declare seems dangerious, like i'm telling it to be a value, when I more so want to retrive a value.

McDoogle
Starting Member

12 Posts

Posted - 2011-03-21 : 16:07:50

Realized I forgot to replace an S with column name. However same error.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-21 : 16:24:20

If INMAST is your table name, then you just need the SELECT:

select reverse(substring(reverse(left(s,30)), charindex(' ',reverse(left(itemdescription1,30))),len(itemdescription1)))
from inmast

The rest of it was just setup to prove the concept.

McDoogle
Starting Member

12 Posts

Posted - 2011-03-21 : 16:40:18

Ah, I see.

Hmm interesting, however this cut off a bunch of the beginning of the fields.

For example: Blank Master Template turned in to "ter Template."

select reverse(substring(reverse(left(s,30)), charindex(' ',reverse(left(itemdescription1,30))),len(itemdescription1)))
from inmast

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-21 : 16:49:59

I'm not 100% sure the logic is completely valid, it's hard to test with a small sample size.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-21 : 17:26:58

[code]
SELECT CASE
WHEN CHARINDEX(' ', itemdescription1, 30) = 0
THEN itemdescription1
ELSE LEFT(itemdescription1, CHARINDEX(' ', itemdescription1, 30) -
CHARINDEX(' ', REVERSE(LTRIM(RTRIM(LEFT(itemdescription1,
CHARINDEX(' ', itemdescription1, 30)))))))
END as shortened
FROM inmast[/code]

McDoogle
Starting Member

12 Posts

Posted - 2011-03-22 : 16:39:18

Thanks Guys,

Russ, unfortuantly your code still cuts words off. Thanks guys I'm just going to ask how my boss does it. I think it's a lot shorter than we think.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-22 : 17:01:02

show me an example that it cuts off.

and what data type is itemdescription1?

McDoogle
Starting Member

12 Posts

Posted - 2011-03-22 : 17:46:43

"Towel Hanger - Portable" gets returned as "Towel Hanger - Portab". The full description isn't more than 30 characters so this should not get cut off at all.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-22 : 18:12:17

A tweak to Russell's code should do the trick

SELECT CASE
WHEN CHARINDEX(' ', itemdescription1, 30) > 0
and DATALENGTH(itemdescription1)>30
THEN LEFT(itemdescription1, CHARINDEX(' ', itemdescription1, 30) -
CHARINDEX(' ', REVERSE(LTRIM(RTRIM(LEFT(itemdescription1,
CHARINDEX(' ', itemdescription1, 30)))))))
ELSE itemdescription1
END as shortened
FROM inmast

Jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-22 : 18:41:56

I'm not sure if it is a consideration, but the code above doesn't work if you were to have a string with a space before the 30 character limit and not after. Contrived sample data: '1234567890123456789012345 6789012345678901234567890'

For that matter what should happen in the string exceeds 30 characters before a space is found (or not found)?

Not 100% what the real requirement is, but just food for thought..

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources