Hi,
In my sql table, there is a column which have string of text :
Column
2ABF CD000000000012345000000000001156
3000000000003452000000000000356
I would like if the 1st character is 2 then out put as
ID Code Num1 Num2 Num3 Num4
2ABF CD 123.45 11.56
if the 1st character is 3 then
ID Code Num1 Num2 Num3 Num4
34.52 3.56
Finally, combine the them for the result
ID Code Num1 Num2 Num3 Num4
2ABF CD 123.45 11.56 34.52 3.56
Please advise if there is a way to do this.
Thanks so much,
Ddee
September 19, 2020 at 11:07 am
You question does not make sense.
If you would care to post a sensible question (with consumable test data!!!!!) someone might give you a sensible answer.
-- *** Consumable test data ***
CREATE TABLE #MyTable
(
MyColumn varchar(100) NOT NULL
);
INSERT INTO #MyTable
VALUES ('2ABF CD000000000012345000000000001156')
,('3000000000003452000000000000356');
-- *** End Consumable test data ***
WITH N(N)
AS
(
SELECT 1 UNION ALL SELECT 2
UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6
)
SELECT
MAX(
CASE
WHEN N.N = 1 AND T.MyColumn LIKE '2%'
THEN LEFT(T.MyColumn, 4)
END
) AS ID
,MAX(
CASE
WHEN N.N = 2 AND T.MyColumn LIKE '2%'
THEN SUBSTRING(T.MyColumn, 8, 2)
END
) AS Code
,MAX(
CASE
WHEN N.N = 3 AND T.MyColumn LIKE '2%'
THEN CAST(CAST(SUBSTRING(T.MyColumn, 10, 15) AS int)/100.0 AS decimal(15,2))
END
) AS Num1
,MAX(
CASE
WHEN N.N = 4 AND T.MyColumn LIKE '2%'
THEN CAST(CAST(SUBSTRING(T.MyColumn, 26, 15) AS int)/100.0 AS decimal(15,2))
END
) AS Num2
,MAX(
CASE
WHEN N.N = 5 AND T.MyColumn LIKE '3%'
THEN CAST(CAST(SUBSTRING(T.MyColumn, 2, 15) AS int)/100.0 AS decimal(15,2))
END
) AS Num3
,MAX(
CASE
WHEN N.N = 6 AND T.MyColumn LIKE '3%'
THEN CAST(CAST(SUBSTRING(T.MyColumn, 17, 15) AS int)/100.0 AS decimal(15,2))
END
) AS Num4
FROM #MyTable T
CROSS JOIN N;
September 21, 2020 at 3:07 pm
Hi Ken,
Sorry, I wasn't very clear.. Below is a better test data and what I try to accomplish. Thanks.
INSERT INTO MyTable
VALUES ('2 0A1234 CD00000000614150000000067823000 000000000000000')
,('3000000003815100000000000000000000000000000000005411500000000000000000000000000')
,('2 0A2345 DE00000000123150000000032478000 000000000000000')
,('3000000004639200000000000000000000000000000000003421500000000000000000000000000')
,('2 0A1256 FW00000000234190000000032517000 000000000000000')
,('2 0A2386 FD00000000212190000000021219000 000000000000000');
And the result I am trying to accomplish:
the 1st 2 rows have all data for column 1-6 as it has row start with 2 and 3 below each other
The last 2 column only have a line start with "2" ( no row start with "3" below it) so the other column is blank
Column1 Column2 Column3 Column4 Column6
0A1234 614.15 678.23 381.51 324.78
0A2345 123.15 324.78 463.92 342.15
0A1256 234.19 325.17
0A2386 212.19 212.19
Thanks so much for your help Ken.
Regards,
Ddee
September 21, 2020 at 3:19 pm
>the 1st 2 rows have all data for column 1-6 as it has row start with 2 and 3 below each other
What do you mean by the first two rows?
How do you link the rows starting with 3 to the corresponding row starting with 2?
Remember a relation (table) is not a list. A relation is an unordered set.
September 21, 2020 at 4:29 pm
Hi Ken,
Column1 Column2 Column3 Column4 Column6
0A1234 614.15 678.23 381.51 324.78
0A2345 123.15 324.78 463.92 342.15
0A1256 234.19 325.17
0A2386 212.19 212.19
2. How do you link the rows starting with 3 to the corresponding row starting with 2?
only if row starting with 2 has corresponding row start with 3 in the next line.
Please advise if it is possible to do this.
Thanks,
Ddee
September 21, 2020 at 4:40 pm
Umm....
Let me try again.
A table is an unordered set so how do you tell what are the first two rows? (Obviously not the order you have listed them in as a table is not a list. ie A table is not like an Excel sheet.)
In your real table maybe they both have a common column which have the same value? Maybe there is a DateAdded or ID column so the order can be determined from that. If so post new CREATE TABLE and INSERT VALUES statements.
September 22, 2020 at 2:58 am
Hi Ken,
There is no relationship between line begin with a 1 and line begin with a 2 except they are next to each other.
So my guess is this cannot be done. Please confirm.
One quick question... is there a way to read those data in sequential order?
Thanks for your help.
Ddee
Unfortunately in terms of relational database theory the rows are not next to each other as rows are unordered.
If the source of the rows is something like a CSV file or Excel sheet then you could add a line number column before importing into SQL Server.
If you only have the table then due to the way SQL server happens to be structured internally you may be able to get away with the following. (ps There are no guarantees this will work especially if the table is large.)
With your latest test data:
-- *** Consumable test data ***
CREATE TABLE #MyTable
(
MyColumn varchar(100) NOT NULL
);
INSERT INTO #MyTable
VALUES ('2 0A1234 CD00000000614150000000067823000 000000000000000')
,('3000000003815100000000000000000000000000000000005411500000000000000000000000000')
,('2 0A2345 DE00000000123150000000032478000 000000000000000')
,('3000000004639200000000000000000000000000000000003421500000000000000000000000000')
,('2 0A1256 FW00000000234190000000032517000 000000000000000')
,('2 0A2386 FD00000000212190000000021219000 000000000000000');
-- *** End Consumable test data ***
Add an identity column:
ALTER TABLE #MyTable
ADD LineNumber int IDENTITY NOT NULL;
Now check the following is in the order you want:
select *
from #MyTable
order by LineNumber;
If the order is correct you can then do the following:
WITH NextColumns
AS
(
SELECT MyColumn
,COALESCE(LEAD(MyColumn) OVER (ORDER BY LineNumber), '') AS MyColumnNext
FROM #MyTable
)
SELECT SUBSTRING(MyColumn, 3, 6) AS ID
,SUBSTRING(MyColumn, 10, 2) AS Code
,CAST(CAST(SUBSTRING(MyColumn, 12, 13) AS int)/100.0 AS decimal(13,2)) AS Num1
,CAST(CAST(SUBSTRING(MyColumn, 25, 13) AS int)/100.0 AS decimal(13,2)) AS Num2
,CASE
WHEN MyColumnNext LIKE '3%'
THEN CAST(CAST(SUBSTRING(MyColumnNext, 2, 13) AS int)/100.0 AS decimal(13,2))
END AS Num3
,CASE
WHEN MyColumnNext LIKE '3%'
THEN CAST(CAST(SUBSTRING(MyColumnNext, 16, 38) AS int)/100.0 AS decimal(38,2))
END AS Num4
,*
FROM NextColumns
WHERE MyColumn LIKE '2%';
September 22, 2020 at 6:46 pm
That works! Thanks so much for your help, Ken.
Regards,
Ddee
September 23, 2020 at 7:36 am
If you have imported the LineNumber into SQL Server then everything should be fine.
If you had to resort to creating the identity column you should, as far as possible, verify the order and document any areas of uncertainty. Just because the result superficially looks fine does not necessarily mean that it is. Areas of uncertainty should also be communicated to the appropriate areas of your organization.
While you sometimes have to cope with what is dumped on you, in future you should try to avoid bad design.
September 23, 2020 at 1:44 pm
Hi Ken,
May I ask, when I import the txt file to SQL using SSIS, if I add a column Linenumber of type identity and let it fill in when importing to SQL table.
Does this the way you said it should be okay?
Thanks,
Ddee
September 23, 2020 at 1:58 pm
I am not sure how SSIS would cope with LineNumber of type identity. Maybe someone with more experience of SSIS can comment.
If you have the data in a text file I would be inclined to add a LineNumber column directly to the text file and then import it. If you do not want to write a script/program to do this it can be done manually in Excel.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply