Have a table with 9 digit dimensioncode...need to make a new table with a 7
digit dimensioncode...is there any way I can
get the name of the first 9 digit dimensioncode and use it as name for the
new 7 digit dimensioncode ?

Without using Cursors ?

Perhaps Cursors isn't a problem with only 50.000 records...?

\Michael Vardinghus

Could you show us some structure, before data and what you want to see post
change?

Cursors are not always a problem but should be avoided if you can.

--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Michael Vardinghus" <michaelvardinghus (AT) notexisting (DOT) com> wrote

Table 1:

Dimcode Dimname
210000000 Olap solution, fase 1
210000001 Olap solution, fase 2
220000001 Building a ship, fase 1
220000002 Building a ship, fase 2

Sadly to say there is no structure in the dimname field so I don't have an
exact number to for ... would just have to set a number and use this ..
could be 15-20 characters. With 15 it would be...(matches my examples but
some will be cut off...)

Table 2: Dimname

2100000 Olap solutition
2200000 Building a ship

\Michael V.


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> skrev i en meddelelse
news:%23RlvodUTEHA.2416 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

The super advanced solution would be to bundle the it into 7 digit packages
and then pull out that part of dimname that is identical on the 9 digit
dimcode's...

But that I don't know how to solve...


"Michael Vardinghus" <michaelvardinghus (AT) notexisting (DOT) com> skrev i en
meddelelse news:urZCjoUTEHA.204 (AT) TK2MSFTNGP10 (DOT) phx.gbl...

From your initial description here you can SUBSTRING the DimName attribute
using CHARINDEX(0 to find the comma.

--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Michael Vardinghus" <michaelvardinghus (AT) notexisting (DOT) com> wrote

You need to have a way of identifying the string some way. From what you
have just said the string can be any format.
If you can consistently say something like "The dimension name is the first
string/2 strings" then sure you can use SUBSTRING

--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Michael Vardinghus" <michaelvardinghus (AT) notexisting (DOT) com> wrote

But i cannot rely on the comma....can substring be used to pull out the
number of characters in too strings
that are identical ?


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> skrev i en meddelelse
news:uSwzM9UTEHA.3016 (AT) tk2msftngp13 (DOT) phx.gbl...

have started after our first discussion....it must be possible to compare to
strings with eachother and pull out what is identical - perhaps i need to
make a function to do so...

USE adhoc
go

DECLARE @tempsag1 int, @tempsag2 int , @tempsagnavn1 varchar (40),
@tempsagnavn2 varchar (40)

DECLARE sagsbudget_cursor CURSOR FOR
SELECT num, [name] from dbo.sagsbudget
order by num, [name]

OPEN sagsbudget_cursor

-- Perform the first fetch.
FETCH NEXT FROM sagsbudget_cursor
into @tempsag1, @tempsagnavn1

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0


BEGIN
-- This is executed as long as the previous fetch succeeds.
if left(@tempsag1,3) = left(@tempsag2,3)
begin
-- here i need to find out how many characters to pull out from the dim
name
end

set @tempsag2 = @tempsag1
set @tempsagnavn2 = @tempsagnavn1
FETCH NEXT FROM sagsbudget_cursor
into @tempsag1, @tempsagnavn1
END

CLOSE sagsbudget_cursor
DEALLOCATE sagsbudget_cursor
GO




"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> skrev i en meddelelse
news:%23TIabCVTEHA.3968 (AT) TK2MSFTNGP09 (DOT) phx.gbl...