I am struggling with selecting specific information out of a strings
-
So I have a feed from my sales order entry system. the first section of the string is the company code. the last two sections are the account. I need to parse the fields so I can do a lookup to our new chart of accounts. the input is as follows. company.account.subaccount. there will always be a company and account. in some instances a subaccount as well. field name from order entry is account number. I need two fields company account.
00010.1351.100
00010.2112
00025.1351.100
00115.1351.100
120010.1351.100
120010.2112
180025.1351.100
1360000.5111.100
1360000.5161.500
1360000.5411
001360000.5111.100
001360000.5161.500
001360000.5411
I need output as follows
company account
XXXXXX YYYY.ZZZ
XXXXXX YYYY
XXXXXX YYYY.ZZZ
etc. etc. -
ok so this gets me the account number
select distinct(Substring([accountnumber],CHARindex('.',[accountnumber])+1,8)) AS JDEAccountnumber
from MAX$
but still struggling on how to get the company
-
Read this article, and download the "DelimitedSplit8k" function.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
You code, based upon the little bit of info you posted, would look something like this:
SELECT *
FROM Max$
CROSS APPLY dbo.DelimitedSplit8K(Max$.ValueToSplit, '.')Which would return something like:
00010.1351.100 1 00010
00010.1351.100 2 1351
00010.1351.100 3 100
00010.2112 1 00010
00010.2112 2 2112
00025.1351.100 1 00025
00025.1351.100 2 1351
00025.1351.100 3 100Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
;WITH myTable AS
(
SELECT * FROM (VALUES ('00010.1351.100'),
('00010.2112'),
('00025.1351.100'),
('00115.1351.100'),
('120010.1351.100'),
('120010.2112'),
('180025.1351.100'),
('1360000.5111.100'),
('1360000.5161.500'),
('1360000.5411'),
('001360000.5111.100'),
('001360000.5161.500'),
('001360000.5411')) T([CompAccSub])
)
SELECT C.Company,
A.Account,
S.SubAccount,
myTable.CompAccSub
FROM myTable
CROSS APPLY(VALUES (LEFT([CompAccSub],CHARINDEX('.',[CompAccSub])-1))) C(Company)
CROSS APPLY(VALUES (Substring([CompAccSub],CHARINDEX('.',[CompAccSub])+1,8))) T([AccSub])
CROSS APPLY(VALUES (CHARINDEX('.',[AccSub]))) I(SubStart)
CROSS APPLY(VALUES (LEFT([AccSub],IIF(I.SubStart>1,I.SubStart-1,100)))) A(Account)
CROSS APPLY(VALUES (SUBSTRING([AccSub],IIF(I.SubStart>1,I.SubStart+1,100),100))) S(SubAccount) -
thanks, both of those answers will take me some time to understand. I ended up just using a brut force method
select(Substring([accountnumber],1,CHARindex('.',[accountnumber])-1)) AS company
,(Substring([accountnumber],CHARindex('.',[accountnumber])+1,8)) AS JDEAccountnumber
from MAX$
-
Try using the Parsename function:
https://docs.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-2017 - nick.hinton - Tuesday, October 23, 2018 9:21 AM
Considering that the OP is using SQL 2008, that is not available to them.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - randyetheridge - Tuesday, October 23, 2018 8:39 AM
Just a different take on a "Brute Force" method...
This code has the test data in place just to prove it works.
SELECT OriginalData = sd.AccountNumber
,Company = SUBSTRING(sd.AccountNumber,1,ca.ploc-1)
,JDEAccountnumber = SUBSTRING(sd.AccountNumber,ca.ploc+1,50)
FROM (VALUES --This is just test data to simulate your table. Replace with simple FROM for your table.
('00010.1351.100')
,('00010.2112')
,('00025.1351.100')
,('00115.1351.100')
,('120010.1351.100')
,('120010.2112')
,('180025.1351.100')
,('1360000.5111.100')
,('1360000.5161.500')
,('1360000.5411')
,('001360000.5111.100')
,('001360000.5161.500')
,('001360000.5411')
) sd (AccountNumber)
CROSS APPLY (SELECT CHARINDEX('.',sd.AccountNumber)) ca (ploc)
;Boiling it down to match your code from the previous post above, it becomes pretty simple.
SELECT Company = SUBSTRING(sd.AccountNumber,1,ca.ploc-1)
,JDEAccountnumber = SUBSTRING(sd.AccountNumber,ca.ploc+1,50)
FROM dbo.YourTable sd
CROSS APPLY (SELECT CHARINDEX('.',sd.AccountNumber)) ca (ploc)
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Michael L John - Tuesday, October 23, 2018 9:24 AM
PARSENAME has been around for a long time, as I recall it. I think he was even available in 2005, although I don't have an instance of that around to check it.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Here's a post about it from 2003:
- nick.hinton - Tuesday, October 23, 2018 9:21 AM
Nice idea!
;WITH myTable AS
(
SELECT * FROM (VALUES ('00010.1351.100'),
('00010.2112'),
('00025.1351.100'),
('00115.1351.100'),
('120010.1351.100'),
('120010.2112'),
('180025.1351.100'),
('1360000.5111.100'),
('1360000.5161.500'),
('1360000.5411'),
('001360000.5111.100'),
('001360000.5161.500'),
('001360000.5411')) T([CompAccSub])
)
SELECT PARSENAME(t.CompAccSub,IIF(P.HasSubAccount=1,3,2)) Company,
PARSENAME(t.CompAccSub,IIF(P.HasSubAccount=1,2,1)) Account,
PARSENAME(t.CompAccSub,IIF(P.HasSubAccount=1,1,NULL)) SubAccount,
t.CompAccSub
FROM myTable t
CROSS APPLY(VALUES (IIF(PARSENAME(t.CompAccSub,3) IS NULL,0,1))) P(HasSubAccount) - ScottPletcher - Tuesday, October 23, 2018 9:32 AM
It says 2012 on the BOL:
https://docs.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-2017PARSENAME (Transact-SQL)
APPLIES TO:
SQL Server (starting with 2012) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse - Jonathan AC Roberts - Tuesday, October 23, 2018 9:38 AMScottPletcher - Tuesday, October 23, 2018 9:32 AM
It says 2012 on the BOL:
https://docs.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-2017PARSENAME (Transact-SQL)
APPLIES TO:SQL Server (starting with 2012) Azure SQL Database Azure SQL Data Warehouse Parallel Data WarehouseOk. It may say that, but it's just not true. It was definitely available before 2012. I used it.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
It works in a 2008 r2 instance, and as I say here is a post about it from 2003:
parsename -
ok the parse code worked perfectly
SELECT Company = SUBSTRING(sd.AccountNumber,1,ca.ploc-1)
,JDEAccountnumber = SUBSTRING(sd.AccountNumber,ca.ploc+1,50)
FROM dbo.Max$ sd
CROSS APPLY (SELECT CHARINDEX('.',sd.AccountNumber)) ca (ploc)I will do some research and reverse engineer the code so I understand how to use it in the future. thanks to all who replied.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply