how to select lower case letters

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

SQL Server 2005 Forums

Transact-SQL (2005)

how to select lower case letters

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-31 : 08:08:16

Hi,
I am using sql server 2005 and I have table of products which has description.

I just modified in all description -> " by in
For ex.
nestle cup 6" is replaced by nestle cup 6in.

Now i want to see all the changes i made in the table.
so iam querying

Select...
from ...
where desc like '%in%'

BUT problem is that I am getting data like :--

NATURE MADE VITAMIN (bcoz it contains IN in the end)

So how to get data like -->
nestle cup 6in and NOT --> NATURE MADE VITAMIN.

Regards,
Sushant
DBA
Virgin Islands(U.K)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-31 : 08:18:32

You can force a case-sensitive collation on the like clause - for example like this:

where desc COLLATE SQL_Latin1_General_CP1_CS_AS like '%in%'

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-31 : 08:40:24

@sunitabeck..
Thanks dear, its working.

But I just noticed that some(very few) of products descriptions is like
"General wine item"
It is lower case also,
so how to differenciate.

I think the changes i made --> nestle cup 6in...
The in comes only after a number; always( the number can be any number)

So, how to get only that rows in which a number comes and then its followed by 'in'

Regards,
Sushant
DBA
Virgin Islands(U.K)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-31 : 08:53:08

like '%[0-9]in%'

No, you're never too old to Yak'n'Roll if you're too young to die.

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-31 : 09:02:11

quote:
Originally posted by webfred

like '%[0-9]in%'

No, you're never too old to Yak'n'Roll if you're too young to die.

Cool.
Tht was nice.

Thanks.

Regards,
Sushant
DBA
Virgin Islands(U.K)

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-02 : 10:35:52

hI,
I have some more queries.
I have some product description whcih are lower case like

post cocoa pebbles 11oz

I want it to change to
POST COCOA PEBBLES 11OZ

I using :--

UPDATE PLU
SET DSPL_DESCR =replace([DSPL_DESCR],'lower(DSPL_DESCR)','UPPER(DSPL_DESCR)')
WHERE DSPL_DESCR <> ''

it says rows affected
but when i see , the changes are not done.

Regards,
Sushant
DBA
Virgin Islands(U.K)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-02 : 10:41:18

just use:
set col = upper(col)

No, you're never too old to Yak'n'Roll if you're too young to die.

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-02 : 11:10:54

Thxs a lot.

Regards,
Sushant
DBA
Virgin Islands(U.K)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources