extract some text - 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

extract some text

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-01-14 : 16:53:33

HI.I need to grab a part of my feild wich starts with 'V' then has some numbers after it.they are not in a specific place on the field.
they look like this:

ProductName
MOTOROLA RAZR V3 CAR CHARGER
MOTOROLA V71 BATT COVER/SLVR
POWER CHARGER MOT V223
KODAK V550 LI-ION 720mAh
.
.
.
and I need these results:
ProductName
V3
V71
V223
V550
.
.
.

can u help me with that.thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-14 : 17:47:10

See PATINDEX and CHARINDEX in books online.

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-01-14 : 17:50:22

ok.Thank u

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 23:29:38

see this as an example


SELECT SUBSTRING(Val,CHARINDEX(' V',Val)+1,CASE WHEN CHARINDEX(' ',Val,CHARINDEX(' V',Val)+1)>0 THEN CHARINDEX(' ',Val,CHARINDEX(' V',Val)+1)-CHARINDEX(' V',Val) ELSE LEN(Val) END)
FROM
(
SELECT 'MOTOROLA RAZR V3 CAR CHARGER' AS Val union all
SELECT 'MOTOROLA V71 BATT COVER/SLVR' UNION ALL
SELECT 'POWER CHARGER MOT V223' UNION ALL
SELECT 'KODAK V550 LI-ION 720mAh'
)t

output
---------------------
V3 
V71 
V223
V550

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-01-15 : 18:20:25

thanks it worked :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 22:56:20

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources