March 19, 2018 at 11:10 am
so I have an account string I need to separate for interfacing to another third party system. I just learned this morning that my original code is not 100% accurate. I was under the impression that the account string was 16 characters. But now I have learned it can be 16, or 17 or 18 characters long. It is separated into 4 components
1234512341234123
12345123412341234
123451234123412345
so the first three segments are fixed, but the last segment can be 3 characters, or 4 characters or 5 characters.
12345-1234-1234-XXXXX
I am thinking this but was wondering if there is a better solution that I am not considering
CASE
When LEN([ACCOUNT]=18
THEN Substring([ACCOUNT],1,5) + "-" + Substring([ACCOUNT],6,4) + "-" + Substring([ACCOUNT],10,4) + "-" + Substring([ACCOUNT],14,5)
When LEN([ACCOUNT]=17
THEN Substring([ACCOUNT],1,5) + "-" + Substring([ACCOUNT],6,4) + "-" + Substring([ACCOUNT],10,4) + "-" + Substring([ACCOUNT],14,4)
ELSE Substring([ACCOUNT],1,5) + "-" + Substring([ACCOUNT],6,4) + "-" + Substring([ACCOUNT],10,4) + "-" + Substring([ACCOUNT],14,3)
END AS Segment1
just wondering if this is the best solution for this issue?
March 19, 2018 at 11:28 am
randyetheridge - Monday, March 19, 2018 11:10 AMso I have an account string I need to separate for interfacing to another third party system. I just learned this morning that my original code is not 100% accurate. I was under the impression that the account string was 16 characters. But now I have learned it can be 16, or 17 or 18 characters long. It is separated into 4 components1234512341234123
12345123412341234
123451234123412345so the first three segments are fixed, but the last segment can be 3 characters, or 4 characters or 5 characters.
12345-1234-1234-XXXXXI am thinking this but was wondering if there is a better solution that I am not considering
CASE
When LEN([ACCOUNT]=18
THEN Substring([ACCOUNT],1,5) + "-" + Substring([ACCOUNT],6,4) + "-" + Substring([ACCOUNT],10,4) + "-" + Substring([ACCOUNT],14,5)
When LEN([ACCOUNT]=17
THEN Substring([ACCOUNT],1,5) + "-" + Substring([ACCOUNT],6,4) + "-" + Substring([ACCOUNT],10,4) + "-" + Substring([ACCOUNT],14,4)
ELSE Substring([ACCOUNT],1,5) + "-" + Substring([ACCOUNT],6,4) + "-" + Substring([ACCOUNT],10,4) + "-" + Substring([ACCOUNT],14,3)
END AS Segment1just wondering if this is the best solution for this issue?
You can use the stuff function, something like this:
😎USE TEEST;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA AS
(
SELECT
X.ID
,X.STRING
FROM (VALUES
(1,'1234512341234123')
,(2,'12345123412341234')
,(3,'123451234123412345')
) X(ID,STRING)
)
SELECT
SD.ID
,SD.STRING
,STUFF(STUFF(STUFF(SD.STRING,14,0,'-'),10,0,'-'),6,0,'-')
FROM SAMPLE_DATA SD;
Output
ID STRING
----------- ------------------ ---------------------
1 1234512341234123 12345-1234-1234-123
2 12345123412341234 12345-1234-1234-1234
3 123451234123412345 12345-1234-1234-12345
March 19, 2018 at 11:42 am
that is good looking. I have never used Stuff command. Let me research, thanks
March 19, 2018 at 11:52 am
ok I missed something. Until I can read and understand "stuff"
I have some account strings with NO group 4 and I just learned we have one group4 with 6 characters
so really I have this
1234512341234
1234512341234123
12345123412341234
123451234123412345
1234512341234123456
can you modify your code to accommodate these two options? I plugged in your code to my SQL and for the most part it worked very well. I would like to use stuff if I can. thanks
March 19, 2018 at 12:01 pm
Like this:
SELECT
Account
,LEN(Account)
,STUFF(STUFF(CASE WHEN LEN(Account) >= 14 THEN STUFF(Account,14,0,'-') ELSE Account END,10,0,'-'),6,0,'-')
FROM (VALUES
('1234512341234')
,('1234512341234123')
,('12345123412341234')
,('123451234123412345')
,('1234512341234123456')
)dt(Account);
March 19, 2018 at 12:10 pm
that is awesome, I have to figure this "stuff" command out. I like it.
March 19, 2018 at 12:27 pm
Another option:
SELECT
Account
,LEN(Account)
,STUFF(STUFF(ISNULL(STUFF(Account,14,0,'-'),Account),10,0,'-'),6,0,'-')
FROM (VALUES
('1234512341234')
,('1234512341234123')
,('12345123412341234')
,('123451234123412345')
,('1234512341234123456')
)dt(Account);
March 19, 2018 at 12:35 pm
Yeah, STUFF is good stuff!
Another thing to note is that you need to STUFF from right to left. Otherwise the stuffed chars throw off the byte locations.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 19, 2018 at 12:44 pm
Yes I have not researched "stuff" yet, but just by looking at your code I figured it was right to left. I am looking forward to learning "stuff"
March 20, 2018 at 2:31 pm
I would store each section in it's own variable character column and use a computed column.
CREATE TABLE dbo.Accts
(
AccNo1 varchar(10)
, AccNo2 varchar(10)
, AccNo3 varchar(10)
, AccNo4 varchar(10)
, AcctNum AS AccNo1 + '-' + AccNo2 + '-' + AccNo3 + '-' + AccNo4 PERSISTED
) ;
[/code]
March 21, 2018 at 9:39 am
Joe thanks for the answer. I will take some time to understand this option
in the short term I have researched Stuff and think I have the basics. I have one more question
I have this string
04110300120000
STUFF(STUFF(STUFF(Stuff(ACCOUNT,11,0,'-'),7,0,'-'),3,0,'-'),1,0,'-') AS segment1,
and it returns this 04-1103-0012-0000
unfortunately the provider of the raw data did not put the information in the correct order, so the real parsing should be
04-0012-1103-0000
as you can see parse fields 2 & 3 are flipped.
I am assuming Stuff only works right to left, so I cannot use stuff in this instance. As I am new to stuff command I wanted to check.
thanks
March 21, 2018 at 9:57 am
randyetheridge - Wednesday, March 21, 2018 9:39 AMJoe thanks for the answer. I will take some time to understand this optionin the short term I have researched Stuff and think I have the basics. I have one more question
I have this string
04110300120000STUFF(STUFF(STUFF(Stuff(ACCOUNT,11,0,'-'),7,0,'-'),3,0,'-'),1,0,'-') AS segment1,
and it returns this 04-1103-0012-0000
unfortunately the provider of the raw data did not put the information in the correct order, so the real parsing should be
04-0012-1103-0000
as you can see parse fields 2 & 3 are flipped.I am assuming Stuff only works right to left, so I cannot use stuff in this instance. As I am new to stuff command I wanted to check.
thanks
Is this true for all the data or just specific items? And no, stuff won't help with flipping segments.
March 21, 2018 at 9:58 am
Lynn Pettis - Wednesday, March 21, 2018 9:57 AMrandyetheridge - Wednesday, March 21, 2018 9:39 AMJoe thanks for the answer. I will take some time to understand this optionin the short term I have researched Stuff and think I have the basics. I have one more question
I have this string
04110300120000STUFF(STUFF(STUFF(Stuff(ACCOUNT,11,0,'-'),7,0,'-'),3,0,'-'),1,0,'-') AS segment1,
and it returns this 04-1103-0012-0000
unfortunately the provider of the raw data did not put the information in the correct order, so the real parsing should be
04-0012-1103-0000
as you can see parse fields 2 & 3 are flipped.I am assuming Stuff only works right to left, so I cannot use stuff in this instance. As I am new to stuff command I wanted to check.
thanks
Is this true for all the data or just specific items? And no, stuff won't help with flipping segments.
Also, it looks like the format of the values changed from your original post.
March 21, 2018 at 10:21 am
yes I have 5 interfaces that come in from 5 different companies. This example is from Mexico the prior was from Netherlands. I wanted to use Stuff, and of course I did. then when mexico reviewed my output file, they noted that parse fields 2 & 3 needed to be swapped.
hence my post. from what I read I did not think stuff would work, but I wanted to confirm. unfortunately the server I am on is 2008 (not 2012) so CONCAT will not work either
looks like CASE When to the rescue :-).
March 21, 2018 at 10:40 am
randyetheridge - Wednesday, March 21, 2018 10:21 AMyes I have 5 interfaces that come in from 5 different companies. This example is from Mexico the prior was from Netherlands. I wanted to use Stuff, and of course I did. then when mexico reviewed my output file, they noted that parse fields 2 & 3 needed to be swapped.hence my post. from what I read I did not think stuff would work, but I wanted to confirm. unfortunately the server I am on is 2008 (not 2012) so CONCAT will not work either
looks like CASE When to the rescue :-).
I think this will work:
DECLARE @testvals TABLE(
Account VARCHAR(32)
);
INSERT INTO @testvals(Account)
VALUES ('04110300120000');
SELECT *
FROM
@testvals AS [t]
CROSS APPLY (SELECT SUBSTRING([t].[Account],3,4),SUBSTRING([t].[Account],7,4)) ca1(segment2,segment3)
CROSS APPLY (SELECT STUFF(STUFF([t].[Account],3,4,[ca1].[segment3]),7,4,[ca1].[segment2])) ca2(FixedAccount)
CROSS APPLY (SELECT STUFF(STUFF(Stuff([ca2].[FixedAccount],11,0,'-'),7,0,'-'),3,0,'-')) ca3(SegmentedAccount);
Edit: Had a copy paste error.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply