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.
Jim SmuldersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
select
   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
0
PortletPaulfreelancerCommented:
there are several ways of splitting strings left() and right() may be used (as I have above) or substring() could be used
0
Jim SmuldersAuthor Commented:
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.
0
Scott PletcherSenior DBACommented:
You don't really need any checks, so I'd avoid them.  I'd especially avoid depending on a specific length in the column, just treat anything after the first four chars as the store id value.

SELECT        LEFT(ACCTID, 4) AS AccountId, NULLIF(SUBSTRING(ACCTID, 5, 10), '') AS Store,
                     ACCTID, FISCALYR, FISCALPERD, TRANSAMT
FROM            dbo.GLPOST
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim SmuldersAuthor Commented:
thanks, simpler for me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.