Jim Smulders
asked on
Splitting a field into two fields, based on the length of its content
Hi, I am a simple SQL view builder.....so please go easy on me. I have a table, that I wish to split one of the fields into 2 different fields, based on the length of data in the field.
Here is my view:
SELECT ACCTID, FISCALYR, FISCALPERD, TRANSAMT
FROM dbo.GLPOST
Where Acctid needs to be split into 2.
the results of this query look like:
ACCTID FISCALYR FISCAL PERIOD TRANSAMT
1000 2014 01 19845.000
1000 2014 01 -1400.000
1000 2014 12 1400.000
10001020 2014 01 300.000
10001020 2014 12 -300.000
In the case above, I would like to create a new accountID, that would just be the first 4 digits
A second field would store the last 4 digits, so in the first 3 records, it would be null or zero
and in the last 2 examples it would be '1020'
Appreciate any assistance....again go easy on me. I just want to edit my view.
Here is my view:
SELECT ACCTID, FISCALYR, FISCALPERD, TRANSAMT
FROM dbo.GLPOST
Where Acctid needs to be split into 2.
the results of this query look like:
ACCTID FISCALYR FISCAL PERIOD TRANSAMT
1000 2014 01 19845.000
1000 2014 01 -1400.000
1000 2014 12 1400.000
10001020 2014 01 300.000
10001020 2014 12 -300.000
In the case above, I would like to create a new accountID, that would just be the first 4 digits
A second field would store the last 4 digits, so in the first 3 records, it would be null or zero
and in the last 2 examples it would be '1020'
Appreciate any assistance....again go easy on me. I just want to edit my view.
there are several ways of splitting strings left() and right() may be used (as I have above) or substring() could be used
ASKER
The query did not work exactly as I expected, and I attach an example of it below. I changed SUBACCTID to STORE, and also repeated the ACCTID in case i would need it as is in the future.
Weirdly, when the field has 8 digits of characters, it does not treat it like 8.....is this because the field properties for this field allow for up to 45 Characters? Whereas when the field has 4 real characters, your query worked fine.
Weirdly, when the field has 8 digits of characters, it does not treat it like 8.....is this because the field properties for this field allow for up to 45 Characters? Whereas when the field has 4 real characters, your query worked fine.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks, simpler for me.
case when len(ACCTID) = 8 then left(ACCTID,4) else ACCTID end as ACCTID
, case when len(ACCTID) = 8 then right(ACCTID,4) else '0' end as SUBACCTID
, FISCALYR, FISCALPERD, TRANSAMT
FROM dbo.GLPOST