MSSQL Procedure Issue with Large Data set

Question

MSSQL Procedure Issue with Large Data set

Ruchita 40

I have a string as

@DataVal = '52050!52051!52055!52052!52053!52058!52059!52056!52057!51400!51887!51765!50558!51647!50678!!51079!50664!51631!52049!51634!51995!51633!51756!51998!350004386!350004393!350004389!350004385@@5@@Yes@@DN_InUse'

SET @ProjectIDs = PARSENAME(REPLACE(@DataVal, '@@', '.'), 4);

This gives null.

But works if length is smaller.

Below output is the first part i.e.

i.e.

@DataVal = '52050!350004393!350004389!350004385@@5@@Yes@@DN_InUse'


Output is : 52050!350004393!350004389!350004385

Yitzhak Khabinsky 27,196 Reputation points

2024-08-07T17:58:05.6266667+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)
LiHongMSFT-4306 31,621 Reputation points

2024-08-08T01:15:21.9566667+00:00

Hi @Ruchita

If any part of the string exceeds 256 bytes, PARSENAME returns NULL for that part.

Please find alternative way from below experts' answers.

If any answer is helpful to your question, you can accept it as an answer so that other users with similar questions can see it more easily. :)

Answer accepted by question author

Viorel 127K

According to documentation, PARSENAME and QUOTENAME cannot work with long strings. Try an alternative:

set @ProjectIDs = substring(@DataVal, 1, charindex('@@', @DataVal) - 1)

Or use STRING_SPLIT on SQL Server 2022.

0 comments

1 additional answer

Your answer

Yitzhak Khabinsky 27,196 Reputation points

2024-08-07T17:58:05.6266667+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)
LiHongMSFT-4306 31,621 Reputation points

2024-08-08T01:15:21.9566667+00:00

Hi @Ruchita

If any part of the string exceeds 256 bytes, PARSENAME returns NULL for that part.

Please find alternative way from below experts' answers.

If any answer is helpful to your question, you can accept it as an answer so that other users with similar questions can see it more easily. :)

Answer 1

Hi @Ruchita,

As @Viorel already pointed out , the PARSENAME() function can handle strings up to 128 chars.

Please try the following solution.

DECLARE @ProjectIDs VARCHAR(1024),
	@DataVal VARCHAR(1024) = '52050!52051!52055!52052!52053!52058!52059!52056!52057!51400!51887!51765!50558!51647!50678!!51079!50664!51631!52049!51634!51995!51633!51756!51998!350004386!350004393!350004389!350004385@@5@@Yes@@DN_InUse';
SET @ProjectIDs = LEFT(@DataVal, CHARINDEX('@@', @DataVal) - 1);
-- test
SELECT @ProjectIDs AS ProjectIDs;

SQL Server 2022 and later solution is below.

SELECT value AS  ProjectIDs
FROM STRING_SPLIT(REPLACE(@DataVal, '@@', '.'), '.',1)
WHERE ordinal = 1;

Share via

MSSQL Procedure Issue with Large Data set

1 additional answer

Your answer