How to TRIM - SQL Server Forums

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

How to TRIM

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-09 : 06:33:49

Hi I have the below paragraph in my datacolum called 'Description'

Ansmann Powerline 5 Super Fast Charger, Microprocessor controlled desktop charger with discharge function for up to 5 NiCd/NiMH cells, For all NiCd and NiMH rechargable batteries including AA/AAA/C/D/9V, Negative Delta V control, faulty cell detection, defined predischarge, trickle charge, separate status indicator for every charging slot, ultra rapid charging, 230V AC. For UK use. Price for 1 Charger.

How do I take only the sentence upto the first comma in my STORED procedure

(ex:Ansmann Powerline 5 Super Fast Charger)

Advance thanks

Vaishu

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-09 : 06:35:27

select left(str, charindex(str,',')-1)

if it's a text column then use substring and patindex instead.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-09 : 06:39:40

Hi

I am new to sql procedure. So can you please tell where I can put datacolum 'description' - Nvarchar(255) in your code.

Thanks

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-09 : 06:43:05

Hi

Sysntax succefull but datacolum 'description' - Nvarchar (1024). So how do I use substring and pathindex. Sorry for trouble

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-09 : 06:44:55

select left(description', charindex(description',',')-1)

Jim

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-09 : 06:51:10

It's not a text - it's char so it should work with charindex and left.

you might want to add
where charindex(',',description) <> 0

had it the wrong way round in the initial post
select left(description, charindex(',', description)-1)
from tbl
where charindex(',',description) <> 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-09 : 08:28:24

Hi

Thanks a lot. That was very very helpful.

Can I use case 'when' comment in the code because I have some description doesn't seperated by comma like below.
If yes how can I use case & when statement.

Ex1. EU Power supply for Digispeed 4 Set 4011012/EU

Ex2.Power-Sonic Sealed Lead-Acid Battery, PS-12550, 12 Volts, 55.0 , Dimensions: 229 x 138 x 205mm, Price for 1

Ex3. Kingston - Flash memory card - 1 GB - SD Memory Card

Ex4.Eveready Super Heavy Duty AA Size 4 Batteries in a Pack Price for 10 Packs

So some of them have comma (,) some of them have not. so how do I use
somthing like
---------------------------
Case description
when ',' then left(description, charindex(',',description)-1) as title,
when '.' left(description, charindex(',',description)-1) as title,
when end of description doesn't have comma then copy all text
else
end as title
-----------------------

Advance thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-09 : 08:44:00

title = case when charindex(',',description) <> 0 then left(description, charindex(',',description)-1)
when charindex('.',description) <> 0 then left(description, charindex('.',description)-1)
else description
end
or simpler
title = case when patindex('%[.,]%',description) <> 0 then left(description, patindex('%[.,]%',description)-1)
else description
end
or even
title = left(description, patindex('%[.,]%',description+'.')-1)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-10 : 04:37:59

Hi Nr

Thanks a lot. It works like a charm. Thank you

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-10 : 07:56:19

Hi nr

Sorry for trouble.

Ansmann Powerline 5 Super Fast Charger, Microprocessor controlled desktop charger with discharge function for up to 5 NiCd/NiMH cells, For all NiCd and NiMH rechargable batteries including AA/AAA/C/D/9V, Negative Delta V control,

How do I take the sentence upto SECOND COMMA and SECOND full stop.

Thank you

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-10 : 07:58:42

Follow up for my last posting

How do I take the sentence upto SECOND COMMA and SECOND full stop for the paragraphs with COMMA seperated and some paragraphs with FULLSTOP seperated

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-10 : 08:01:06

Are you v2000 or v2005?

What about
aaaaaaa,bbbbbbb.ccccccc,ddddddd

would you want
aaaaaaa,bbbbbbb
or
aaaaaaa,bbbbbbb.ccccccc
or could that never happen?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-10 : 08:58:52

Hi
I have description datacolum and it has the following paragraphs. Some of them are seprated by comma(,) and some of them by fullstop (.) and some of them have full stop at the end of paragraph.

ex1. aaaaaa, ddhhd, jsdhfjh , djfhjks.
I need like aaaaaa, ddhhd only from the above sentance

ex2. aaaaa.sfsddd.dfggg.gggg.fttt.
I need aaaaa.sfsddd

ex3. aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa.
I need aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-10 : 09:06:00

Use REPLACE(Col1, '.', ',') to get all delimiters the same
Use CHARINDEX to get position of comma

Or simply (with formatting)
SELECT dbo.fnParseString(-1, ',', REPLACE(Col1, '.', ',')) + ', ' + dbo.fnParseString(-2, ',', REPLACE(Col1, '.', ','))
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-10 : 09:26:57

I am getting the below error for the code I used

code
dbo.fnParseString(-1, ',', REPLACE(description, '.', ',')) + ', ' + dbo.fnParseString(-2, ',', REPLACE(description, '.', ',')) as tittle,

error
Server: Msg 208, Level 16, State 1, Procedure AMZSelCen, Line 18
Invalid object name 'dbo.fnParseString'.
Server: Msg 208, Level 16, State 1, Procedure AMZSelCen, Line 18
Invalid object name 'dbo.fnParseString'.
Stored Procedure: Dass.dbo.AMZSelCen

I thing I am missing somthing dbo.fnParseString

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-10 : 12:49:06

fnParseString is found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

E 12°55'05.25"
N 56°04'39.16"

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-13 : 04:27:00

Hi
Thanks a lot to NR and Peso.

Its fantastic and works as I wanted.

quote:
Originally posted by Peso

fnParseString is found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources