如何在sql server中使用storedprocedure从字符串中获取子字符串 [英] how to get substring from string using storedprocedure in sql server

问题描述

我有这样的表数据：

I have table data like this:

sno             component           subcomponent               Irnumber
1                   1                 c1 to c100                 001
2                   1                 c200 to c444               002
3                   1                 c450,c451,c455             003

在文本框中，用户输入c50 ....我需要检索第一条记录

here in the text box user enters c50....i need to retrieve first record

1              1              c1 to c100            001

请帮助我。

please help me.

推荐答案

不要那样存储。

SQL不擅长字符串操作，你需要找到两个数字，提取它们，验证它们，将它们转换为整数，然后比较它们。

相反，更改数据库使用两个整数列（可能还有一个NVARCHAR列作为前缀）并使用BETWEEN：

Don't store it like that.
SQL is not good at string manipulations, and you need to find two numbers, extract them, validate them, convert them to an integer, and then compare them.
Instead, change your database to use two integer columns (and possibly an NVARCHAR column for the prefix) and use BETWEEN:

sno component prefix subcomponentStart subcomponentEnd Irnumber
1           1      C                 1             100      001

SELECT * FROM MyTable WHERE Prefix='C' AND ValueFromUser BETWEEN subcomponentStart AND subcomponentEnd

这篇关于如何在sql server中使用storedprocedure从字符串中获取子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！