How to capture the last column data with SQL Server to insert into database

Question

I would like to get the last column data and insert into database.

This is the sample data.

================================= Yield =======================================================
                   S1|     S2|     S3|    S4|       S5|     S6|     S7| Pensoft|
.# Inspected =|  2506|   2507|   2500|     0|     2487|   2513|   2691|  2535|
.# Pass      =|  2495|   2480|   2485|     0|     2487|   2513|   2691|  2503|
.# Fail      =|    11|      0|     14|     0|        0|      0|      0|    26|
.# Invalid   =|     0|     27|      1|     0|        0|      0|      0|     6|
.Yield       =| 99.56|  98.92|  99.40|  0.00|   100.00| 100.00| 100.00| 98.73|

In the table there is "Pensoft" column. I need to capture the "Inspected" row value in Pensoft column. The "Pensoft" column is total. I can't get the value into the database it keep showing

Error converting data type varchar to numeric.

Below is my code. Is it I have missing something?

ELSE IF @ls_long_line LIKE '% S1| S2| S3| S4| S5| S6| S7| Pensoft|%'  
BEGIN
    SELECT @li_total_index = charindex('Pensoft',@ls_long_line) -1
END
    
ELSE IF CHARINDEX('.# Inspected =|' ,@ls_long_line) > 0
BEGIN
    print @ls_long_line
    select @li_pos = charindex('|',@ls_long_line)   

    --INSPECTED---
    select @li_pos = charindex('|',@ls_long_line,@li_pos) 
    select @ls_temp = replace(ltrim(rtrim(SUBSTRING (@ls_long_line,@li_pos,len(@ls_long_line)))) ,' ','')
    SELECT @ls_total_units_tested = convert(decimal(9,0),@ls_temp)
END

I get this messages when I execute it. May I know where did I did wrong and how can I change it? This is to read text file data and the value will be in 5 digit also. How can I get it on the last column data?

FileName :D:\TESTENG\DATA\BACKEND\PENTA\P0248591_NOV92020_0H47M_SERVER.TXT
.# INSPECTED =| 2506|   2507|   2500|   0|  2487|   2513|   2691|   2535|
Msg 8114, Level 16, State 5, Procedure load_PENTA, Line 320
Error converting data type varchar to numeric.

Note that the perceived _order_, as denoted by the comment e.g. `# Inspected`, does not actually exist unless there be a real column which provides that order. — Tim Biegeleisen, Dec 07 '20 at 09:04
Is your sample data a table or one single line string? According to your query, you are only processing the header line (S1 | S2 | ...) - there is nothing numeric in this string... — Tyron78, Dec 07 '20 at 09:12
what do you get when you print `@ls_temp` before the last `SELECT` statement? — cristelaru vitian, Dec 07 '20 at 09:13
@Javier then I guess you are simply processing the wrong row. The string `LIKE '% S1| S2| S3| S4| S5| S6| S7| Pensoft|%' ` does not contain `.# inspected' ... — Tyron78, Dec 07 '20 at 09:17
when you do the `charindex` function, do you intend to get the index of the last occurance of the '|' character in `@ls_long_line` string (aka the last index)? — cristelaru vitian, Dec 07 '20 at 09:49
Why did you delete your entire question except for the first sentence? That single sentence is **not** a properly formatted question with all required information! — Sander, Dec 08 '20 at 11:15

score 0 · Answer 1 · answered Dec 07 '20 at 09:47

Unlike what you said before, your code handles your input like it comes from a text file and not from an existing (sample) table in your database... You are looping over the input lines and storing them in @ls_long_line and parsing each line (whether it contains a header or values).

Reproduction

Removing the header bit and adding some comments gives this bit of runnable code that reproduces your error:

-- input
declare @ls_long_line nvarchar(max) = '.# Inspected =| 2506|   2507|   2500|   0|  2487|   2513|   2691|   2535|';
select @ls_long_line as Input;

-- process
declare @li_pos int;
declare @ls_temp nvarchar(100);
declare @ls_total_units_tested decimal(9,0);

IF CHARINDEX('.# Inspected =|', @ls_long_line) > 0
BEGIN
    -- (1) find starting |
    select @li_pos = charindex('|', @ls_long_line);
    -- (2) find next |
    select @li_pos = charindex('|', @ls_long_line, @li_pos);
    
    -- (3) remove row label and spaces
    declare @substr nvarchar(100) = SUBSTRING(@ls_long_line, @li_pos, len(@ls_long_line));
    select @ls_temp = replace(ltrim(rtrim(@substr)),' ' , '');
    
    -- (4) intermediate result
    select @ls_temp as Intermediate;
    
    -- (5) attempt to convert intermediate result to numeric value
    SELECT @ls_total_units_tested = convert(decimal(9,0), @ls_temp);
END

-- result
select @ls_total_units_tested as Result;

My additional comments (1) and (2) already show where you are going wrong. Commenting out the select from part (5) will show the intermediate result @ls_temp:

|2506|2507|2500|0|2487|2513|2691|2535|

This entire string value obviously cannot be converted to a single number, which explains your error.

Solution

Fix parts (1) and (2) to get the proper positions of the last two separators |.
Correct part (3) to select a substring with only the right numbers (using the positions from step 1.).
Converting the substring in part (5) should no longer produce an error.

Additional comments:

Why use a decimal with 0 digits after the decimal place (decimal(9,0))? Using an int looks more appropriate.
If your SQL Server version allows it (2016 and higher), then the STRING_SPLIT() function could help you to parse the columns.

score 0 · Answer 2 · answered Dec 07 '20 at 10:00

To get the value between the last two | characters, after the print @ls_long_line line, you can use the following code (I used this solution to construct the code: Is there a LastIndexOf in SQL Server?):

declare @ls_long_line_Temp as varchar(MAX)
select @ls_long_line_Temp = left(@ls_long_line, len(@ls_long_line) - charindex('|', reverse(@ls_long_line) + '|'))
select @ls_temp = right(@ls_long_line_Temp, charindex('|', reverse(@ls_long_line_Temp) + '|') - 1)

How to capture the last column data with SQL Server to insert into database

2 Answers2