get portion of a feild - 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

get portion of a feild

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-12-23 : 15:15:29

hi.I have a cloumn with these values:

SANYO SCP-5300 LI-ION 950mAh
SANYO SCP-5300 LI-ION 1400mAh
MOTOROLA V66 LI-ION 600mAh/BLU
MOTOROLA T720 LI-ION 1.2Ah/SLV
NEXTEL i95cl LI-ION 1.4Ah/SLVR
NEXTEL i530 LI-ION 1400mAh
.
.
.

first I need to get the last part of the field which are:
BLU
SLV
SLVR
.
.

then get the second section from end of the feilds wich are:
950mAh
1400mAh
600mAh
1.2Ah
1.4Ah
1400mAh
.
.
.

plz help me.thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-12-23 : 15:36:11

You're going to need to define what delimits your string into seperate values. Those definitions needs to apply to all the data values you want to split.

Look at books online for string functions. Some ones you will likey use are:
substring
replace
right
left
reverse

Give it a try and post any specific questions you have. It would be helpful if you post code to create a #temp table with sample data

You can also search the forum for phrases like "split", "break apart", or "parse" for similar solutions.

Be One with the Optimizer
TG

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-12-23 : 17:54:54

thanks for the reply.
I use this query:
SELECT REVERSE(LEFT(REVERSE(product_name), CHARINDEX(' ',

REVERSE(product_name)) - 1)) AS pname, product_name
FROM tbl_product

get the results of :
950mAh
1400mAh
600mAh/BLU
1.2Ah/SLV
1.4Ah/SLVR
1400mAh

but still I dont know how to get the values after / like :

BLU & SLV ,....
and also I need to get the second values after sapce from right like:
L-ION
LI-ION

please help me Im new...big thanks all of you
it's very urgent

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-23 : 19:03:24

DECLARE @t TABLE(
MODEL VARCHAR(80)
)

INSERT @t
SELECT 'SANYO SCP-5300 LI-ION 950mAh'
UNION ALL
SELECT 'SANYO SCP-5300 LI-ION 1400mAh'
UNION ALL
SELECT 'MOTOROLA V66 LI-ION 600mAh/BLU'
UNION ALL
SELECT 'MOTOROLA T720 LI-ION 1.2Ah/SLV'
UNION ALL
SELECT 'NEXTEL i95cl LI-ION 1.4Ah/SLVR'
UNION ALL
SELECT 'NEXTEL i530 LI-ION 1400mAh'

SELECT CASE
WHEN MODEL LIKE '[a-z]%/[a-z]%' THEN SUBSTRING(REVERSE(LEFT(REVERSE(MODEL),CHARINDEX(' ',REVERSE(MODEL)) - 1)),
1,(CHARINDEX('/',REVERSE(LEFT(REVERSE(MODEL),CHARINDEX(' ',REVERSE(MODEL)) - 1)))) - 1)
ELSE (REVERSE(LEFT(REVERSE(MODEL),CHARINDEX(' ',REVERSE(MODEL)) - 1)))
END
FROM @t

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-23 : 19:24:47

Declare @t table
(Model varchar(80))
insert @t
select 'SANYO SCP-5300 LI-ION 950mAh' union all
select 'SANYO SCP-5300 LI-ION 1400mAh' union all
select 'MOTOROLA V66 LI-ION 600mAh/BLU' union all
select 'MOTOROLA T720 LI-ION 1.2Ah/SLV' union all
select 'NEXTEL i95cl LI-ION 1.4Ah/SLVR' union all
select 'NEXTEL i530 LI-ION 1400mAh'

SELECT case when model like '[a-z]%/[a-z]%' then substring(model,charindex('/',model)+1,len(left(reverse(model),charindex('/',reverse(model))-1))) else null end
from @t

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-12-23 : 19:27:20

thank u so much sodeep.
it works fine but what if I have 50000 records?how should I write a query which works for all the records?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-23 : 22:12:07

what happen when you tried?

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-24 : 02:22:31

Hopefully, this will be of some help:

while @slashcount <> 0
begin
set @var1 = substring(@var1,(charindex('/',@var1,1))+1,len(@var1))
set @slashcount = @slashcount - 1
end

I am not SQL literate enough to adapt this specifically to your needs, but if you can't do it, maybe someone else can tweak it to your needs...
Basically it takes you to the last / in the string.

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources