Array? Not even sure what to call...

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

Array? Not even sure what to call...

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-04 : 12:16:44

So we store combo-sets for our part numbers like
part1_part2_part3

and I need to get a combined cost for those items, and the first trick will be to pull those part numbers apart.

So I'm trying this out:

declare @myval varchar(1000)
set @myval='tb-001_tb-002_lh002_026-b'
select left(@myval,charindex('_',@myval)-1)

union
select left((right(@myval, LEN(@myval)-(charindex('_',@myval)-1)-1)),charindex('_',(right(@myval, LEN(@myval)-(charindex('_',@myval)-1)-1)))-1)

union
select left(right(@myval, (len(left((right(@myval, LEN(@myval)-(charindex('_',@myval)-1)-1)),charindex('_',(right(@myval, LEN(@myval)-(charindex('_',@myval)-1)-1)))-1))+len(left(@myval,charindex('_',@myval)-1))-1)), CHARINDEX('_',right(@myval, (len(left((right(@myval, LEN(@myval)-(charindex('_',@myval)-1)-1)),charindex('_',(right(@myval, LEN(@myval)-(charindex('_',@myval)-1)-1)))-1))+len(left(@myval,charindex('_',@myval)-1))-1)))-1)

I'm really trying to pull out 026-b as well, AND it needs to be flexible, because @myval could be more or less items split apart by '_'. But can you see what I'm trying to do with my infantile coding?

Thoughts much appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 12:30:30

use a string parsing function like below :-

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

then use like


SELECT SUM(f.Val*1)
FROM Table t
CROSS APPLY dbo.ParseValues(t.field,'_')f
GROUP BY t.Field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-04 : 12:40:58

I can't create functions, I'm working on a back-end server that doesn't allow the creation of functions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 12:44:11

what? what kind if server is that? or is it that you dont have access?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-04 : 12:51:58

Yeah I don't have access...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 12:55:26

then use a tally table like below

http://www.sqlservercentral.com/articles/T-SQL/62867/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-04 : 12:57:34

I'll look into this. Thanks for your help! This seems a little over my head.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 12:59:45

ok. get back if you feel it difficult

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-04 : 13:48:08

This won't work so good with indexes, but it might be a solution if you cannot create functions:

DECLARE @Table TABLE(ID INT, Part VARCHAR(100))
INSERT @Table
SELECT 1, 'part1'
UNION ALL SELECT 2, 'part2'
UNION ALL SELECT 3, 'part3'
UNION ALL SELECT 4, 'part4'
UNION ALL SELECT 5, 'part5'

DECLARE @Val VARCHAR(100)
SET @Val = 'part1_part2_part3'

SELECT *
FROM @Table AS T
WHERE '_' + @Val + '_' LIKE '%_' + Part + '_%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources