Get particular value from string in SQL query

Question

I am working with a stored procedure where I am getting a string with different characters I want to get a value at last index

[AV] Z_Prem_454-3000_XXXXX_800+ [InstalmentScheme 6]

above is the string I am getting from a external db

I want the value of instalmentscheme which is 6 above

I am currently sending the above whole string from stored procedure to C# code and there I have written a extension method called extract which gets above string and gets the value of the instalemntscheme code I don't know this will work if value is null or sometimes null

public static string Extract(string input)
{
    if (string.IsNullOrEmpty(input)) return null;
    var lastIndex = input.LastIndexOf(' ');
    if (lastIndex < 0) return "";

    var code = input[lastIndex..^1].Trim();

    return CheckingIfNumber(code) ? code : null;
}

I want to do it in SQL itself so that I can send that to C# code directly.

You'll need to show us the SQL code which is currently generating that string then, and the source data it comes from. I'm really hoping the data isn't saved like that in a single column of a table, and this is just some construction made by the stored procedure. — ADyson
– ADyson, Commented Apr 25, 2024 at 7:17
Parsing this string should be the last resort, used only if the database design is really so bad that important information is stored as an opaque string instead of using a proper field. You could use a simple regular expression go catch the installment part at the end, eg \[InstalmentScheme \d+\]$. You can capture just the number into a capture group with \[InstalmentScheme (\d+)\]$ and even give it a name so you can access it by name \[InstalmentScheme (?<scheme>\d+)\]$ — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 25, 2024 at 9:19
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 code 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;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Apr 25, 2024 at 12:10

Panagiotis Kanavos · Accepted Answer · 2024-04-25 10:04:08Z

The best solution would be to fix the code or table design that returns this complicated string, store the parts in proper columns and return the correct column directly. Not try to parse the string after the fact. Parsing prevents the use of indexes, forcing a query to scan the entire table to find matches.

The second best option, especially if only a single value needs to be parsed, is to use a regular expression in C#, not substring operations. The following expression will match the InstalmentScheme value at the end of the string, taking account of any whitespace, and extract it in a named group:

   var pattern=@"\[InstalmentScheme\s*(?<scheme>\d+)\s*\]$";
   var match=Regex.Match(input,pattern);
   if (m.Success)
   {
       var scheme=m.Groups["scheme"]; 
   }

T-SQL doesn't have regular expressions like other databases. Support was announced for 2025, with syntax similar to MySQL.

The last resort is to use T-SQL to retrieve the substring between [InstalmentScheme and the end of the string except the last character. This can easily break if there's additional whitespace :

select substring(@value,
    charindex('[InstalmentScheme ',@value)+len('[InstalmentScheme ')+1,
    LEN(@value)-charindex('[InstalmentScheme ',@value)-len('[InstalmentScheme ')-1)

This expression starts reading from the location after after [InstalmentScheme ', returned using charindex('[InstalmentScheme ',@value)+len('[InstalmentScheme ')+1 up to the end of the string. T-SQL doesn't have a function that returns the string between two locations, so we need to calculate the end location as Length - PatternEnd -1. That's what this does:

LEN(@value)-charindex('[InstalmentScheme ',@value)-len('[InstalmentScheme ')-1

Yitzhak Khabinsky · Accepted Answer · 2024-04-25 12:12:11Z

Please try the following solution based on tokenization via SQL Server's XML and XQuery functionality.

I am getting a string with different characters I want to get a value at last index

Notable points:

CROSS APPLY is tokenizing input column as XML.
XPath predicate /root/r[last()] is retrieving a last token from the string of tokens, exactly what you need.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(200));
INSERT @tbl VALUES
('[AV] Z_Prem_454-3000_XXXXX_800+ [InstalmentScheme 6]')
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

SELECT *
    , TRIM(']' FROM c.value('(/root/r[last()]/text())[1]', 'VARCHAR(10)')) AS result
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
    REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
    ']]></r></root>' AS XML)) AS t1(c);

Output

ID	tokens	result
1	[AV] Z_Prem_454-3000_XXXXX_800+ [InstalmentScheme 6]	6

Collectives™ on Stack Overflow

Get particular value from string in SQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related