sql - खंड में एक एसक्यूएल पैरामीटर





sql-server parameters (25)


मैं एक टेबल प्रकार पैरामीटर (क्योंकि यह SQL सर्वर 2008 है ) पास करेगा, और where exists , या आंतरिक शामिल हों। आप sp_xml_preparedocument का उपयोग करके एक्सएमएल का भी उपयोग कर sp_xml_preparedocument , और फिर उस अस्थायी तालिका को भी इंडेक्स कर सकते हैं।

मैं तर्कसंगत संख्या के साथ एक IN क्लॉज युक्त क्वेरी को पैरामीटर कैसे करूं, इस तरह की?

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

इस प्रश्न में, तर्कों की संख्या 1 से 5 तक कहीं भी हो सकती है।

मैं इस (या एक्सएमएल) के लिए समर्पित संग्रहीत प्रक्रिया का उपयोग नहीं करना चाहूंगा, लेकिन अगर SQL सर्वर 2008 के लिए विशिष्ट कुछ शानदार तरीका है, तो मैं इसके लिए खुला हूं।




In SQL Server 2016+ another possibility is to use the OPENJSON function.

This approach is blogged about in OPENJSON - one of best ways to select rows by list of ids .

A full worked example below

CREATE TABLE dbo.Tags
  (
     Name  VARCHAR(50),
     Count INT
  )

INSERT INTO dbo.Tags
VALUES      ('VB',982), ('ruby',1306), ('rails',1478), ('scruffy',1), ('C#',1784)

GO

CREATE PROC dbo.SomeProc
@Tags VARCHAR(MAX)
AS
SELECT T.*
FROM   dbo.Tags T
WHERE  T.Name IN (SELECT J.Value COLLATE Latin1_General_CI_AS
                  FROM   OPENJSON(CONCAT('[', @Tags, ']')) J)
ORDER  BY T.Count DESC

GO

EXEC dbo.SomeProc @Tags = '"ruby","rails","scruffy","rubyonrails"'

DROP TABLE dbo.Tags 



SQL Server 2016+ आप SPLIT_STRING फ़ंक्शन का उपयोग कर सकते हैं:

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';

SELECT * 
FROM Tags
WHERE Name IN (SELECT [value] FROM STRING_SPLIT(@names, ','))
ORDER BY Count DESC;

या:

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';

SELECT t.*
FROM Tags t
JOIN STRING_SPLIT(@names,',')
  ON t.Name = [value]
ORDER BY Count DESC;

LiveDemo

स्वीकार्य उत्तर निश्चित रूप से काम करेगा और यह जाने का एक तरीका है, लेकिन यह विरोधी पैटर्न है।

ई। मूल्यों की सूची से पंक्तियां खोजें

यह सामान्य एंटी-पैटर्न के लिए प्रतिस्थापन है जैसे अनुप्रयोग परत या ट्रांजैक्ट-एसक्यूएल में गतिशील एसक्यूएल स्ट्रिंग बनाना, या LIKE ऑपरेटर का उपयोग करके:

SELECT ProductId, Name, Tags
FROM Product
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';

मूल प्रश्न के लिए SQL Server 2008 की आवश्यकता है। चूंकि इस प्रश्न को अक्सर डुप्लिकेट के रूप में उपयोग किया जाता है, इसलिए मैंने इस उत्तर को संदर्भ के रूप में जोड़ा है।




I use a more concise version of the top voted answer :

List<SqlParameter> parameters = tags.Select((s, i) => new SqlParameter("@tag" + i.ToString(), SqlDbType.NVarChar(50)) { Value = s}).ToList();

var whereCondition = string.Format("tags in ({0})", String.Join(",",parameters.Select(s => s.ParameterName)));

It does loop through the tag parameters twice; but that doesn't matter most of the time (it won't be your bottleneck; if it is, unroll the loop).

If you're really interested in performance and don't want to iterate through the loop twice, here's a less beautiful version:

var parameters = new List<SqlParameter>();
var paramNames = new List<string>();
for (var i = 0; i < tags.Length; i++)  
{
    var paramName = "@tag" + i;

    //Include size and set value explicitly (not AddWithValue)
    //Because SQL Server may use an implicit conversion if it doesn't know
    //the actual size.
    var p = new SqlParameter(paramName, SqlDbType.NVarChar(50) { Value = tags[i]; } 
    paramNames.Add(paramName);
    parameters.Add(p);
}

var inClause = string.Join(",", paramNames);



आप पैरामीटर को स्ट्रिंग के रूप में पास कर सकते हैं

तो आपके पास स्ट्रिंग है

DECLARE @tags

SET @tags = ‘ruby|rails|scruffy|rubyonrails’

select * from Tags 
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc

तो आपको केवल 1 पैरामीटर के रूप में स्ट्रिंग पास करना है।

यहां उपयोग किए जाने वाले विभाजन कार्य हैं।

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END



मैं डिफ़ॉल्ट रूप से एक तालिका मूल्यवान फ़ंक्शन (जो एक स्ट्रिंग से एक तालिका लौटाता है) को पास करने के साथ डिफ़ॉल्ट रूप से संपर्क करता हूं।

यहां यूडीएफ के लिए कोड है (मुझे इसे स्टैक ओवरफ़्लो से कहीं मिला है, मुझे अभी स्रोत नहीं मिल रहा है)

CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT 
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )

एक बार जब आप इसे प्राप्त कर लेंगे तो आपका कोड उतना आसान होगा जितना:

select * from Tags 
where Name in (select s from dbo.split(';','ruby;rails;scruffy;rubyonrails'))
order by Count desc

जब तक आप एक हास्यास्पद लंबी स्ट्रिंग नहीं है, यह तालिका सूचकांक के साथ अच्छी तरह से काम करना चाहिए।

If needed you can insert it into a temp table, index it, then run a join...




मेरी राय में, इस समस्या को हल करने का सबसे अच्छा स्रोत, इस साइट पर पोस्ट किया गया है:

Syscomments। दीनाकर नेठी

CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS 
BEGIN
 --DECLARE @T Table (col1 varchar(50))  
 -- @Array is the array we wish to parse
 -- @Separator is the separator charactor such as a comma
 DECLARE @separator_position INT -- This is used to locate each separator character
 DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
 -- For my loop to work I need an extra separator at the end. I always look to the
 -- left of the separator character for each array value

 SET @array = @array + @separator

 -- Loop through the string searching for separtor characters
 WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
 BEGIN
    -- patindex matches the a pattern against a string
    SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
    SELECT @array_value = LEFT(@array, @separator_position - 1)
    -- This is where you process the values passed.
    INSERT into @T VALUES (@array_value)    
    -- Replace this select statement with your processing
    -- @array_value holds the value of this element of the array
    -- This replaces what we just processed with and empty string
    SELECT @array = STUFF(@array, 1, @separator_position, '')
 END
 RETURN 
END

उपयोग:

SELECT * FROM dbo.fnParseArray('a,b,c,d,e,f', ',')

क्रेडिट्स: दीनाकर नेठी




Another possible solution is instead of passing a variable number of arguments to a stored procedure, pass a single string containing the names you're after, but make them unique by surrounding them with '<>'. Then use PATINDEX to find the names:

SELECT * 
FROM Tags 
WHERE PATINDEX('%<' + Name + '>%','<jo>,<john>,<scruffy>,<rubyonrails>') > 0



In ColdFusion we just do:

<cfset myvalues = "ruby|rails|scruffy|rubyonrails">
    <cfquery name="q">
        select * from sometable where values in <cfqueryparam value="#myvalues#" list="true">
    </cfquery>



आईएमएचओ एक चरित्र स्ट्रिंग में सूची को स्टोर करने का उचित तरीका है (डीबीएमएस समर्थन द्वारा लंबाई में सीमित); एकमात्र चाल यह है कि (प्रसंस्करण को सरल बनाने के लिए) मेरे पास शुरुआत में और स्ट्रिंग के अंत में एक विभाजक (मेरे उदाहरण में एक अल्पविराम) है। विचार "फ्लाई पर सामान्यीकृत" करना है, सूची को एक-कॉलम तालिका में बदलना जिसमें प्रति पंक्ति एक पंक्ति शामिल है। यह आपको बारी करने की अनुमति देता है

में (सीटी 1, सीटी 2, सीटी 3 ... सीटीएन)

एक में

में (चुनें ...)

या (समाधान जो मैं संभवतः पसंद करता हूं) नियमित रूप से शामिल होता है, यदि आप सूची में डुप्लिकेट मानों के साथ समस्याओं से बचने के लिए बस "विशिष्ट" जोड़ते हैं।

दुर्भाग्यवश, एक स्ट्रिंग को टुकड़ा करने की तकनीकें काफी उत्पाद-विशिष्ट हैं। यहां SQL सर्वर संस्करण है:

 with qry(n, names) as
       (select len(list.names) - len(replace(list.names, ',', '')) - 1 as n,
               substring(list.names, 2, len(list.names)) as names
        from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as list
        union all
        select (n - 1) as n,
               substring(names, 1 + charindex(',', names), len(names)) as names
        from qry
        where n > 1)
 select n, substring(names, 1, charindex(',', names) - 1) dwarf
 from qry;

ओरेकल संस्करण:

 select n, substr(name, 1, instr(name, ',') - 1) dwarf
 from (select n,
             substr(val, 1 + instr(val, ',', 1, n)) name
      from (select rownum as n,
                   list.val
            from  (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val
                   from dual) list
            connect by level < length(list.val) -
                               length(replace(list.val, ',', ''))));

और MySQL संस्करण:

select pivot.n,
      substring_index(substring_index(list.val, ',', 1 + pivot.n), ',', -1) from (select 1 as n
     union all
     select 2 as n
     union all
     select 3 as n
     union all
     select 4 as n
     union all
     select 5 as n
     union all
     select 6 as n
     union all
     select 7 as n
     union all
     select 8 as n
     union all
     select 9 as n
     union all
     select 10 as n) pivot,    (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val) as list where pivot.n <  length(list.val) -
                   length(replace(list.val, ',', ''));

(बेशक, "पिवट" को कई पंक्तियों को वापस करना होगा क्योंकि सूची में हमें अधिकतम संख्या में आइटम मिल सकते हैं)




मैंने जेफ / जोएल आज पॉडकास्ट पर इस बारे में बात की ( एपिसोड 34 , 2008-12-16 (एमपी 3, 31 एमबी), 1 एच 03 मिनट 38 सेकंड - 1 एच 06 मिनट 45 सेकेंड), और मैंने सोचा कि मुझे स्टैक ओवरफ़्लो याद आया LINQ to SQL का उपयोग कर रहा था, लेकिन शायद यह हटा दिया गया था। LINQ से SQL में वही बात है।

var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };

var results = from tag in Tags
              where inValues.Contains(tag.Name)
              select tag;

बस। और, हाँ, LINQ पहले से ही पीछे की तरफ देखता है, लेकिन Contains खंड अतिरिक्त मेरे पीछे दिखाई देता है। जब मुझे काम पर एक परियोजना के लिए एक समान प्रश्न करना पड़ा, तो मैंने स्वाभाविक रूप से स्थानीय सरणी और SQL सर्वर तालिका के बीच जुड़कर गलत तरीके से ऐसा करने की कोशिश की, LINQ से SQL अनुवादक को समझने के लिए पर्याप्त स्मार्ट होगा किसी भी तरह अनुवाद। ऐसा नहीं हुआ, लेकिन यह एक त्रुटि संदेश प्रदान करता था जो वर्णनात्मक था और मुझे उपयोग का उपयोग करने की ओर इशारा करता था।

वैसे भी, यदि आप इसे अत्यधिक अनुशंसित LINQPad में चलाते हैं, और इस क्वेरी को चलाते हैं, तो आप SQL LINQ प्रदाता जेनरेट किए गए वास्तविक SQL को देख सकते हैं। यह आपको प्रत्येक खंड को पैरामीटर में पैरामीटर किए जाने वाले मान दिखाएगा।




Here is another answer to this problem.

(6/4/13 को नया संस्करण पोस्ट किया गया)।

    private static DataSet GetDataSet(SqlConnectionStringBuilder scsb, string strSql, params object[] pars)
    {
        var ds = new DataSet();
        using (var sqlConn = new SqlConnection(scsb.ConnectionString))
        {
            var sqlParameters = new List<SqlParameter>();
            var replacementStrings = new Dictionary<string, string>();
            if (pars != null)
            {
                for (int i = 0; i < pars.Length; i++)
                {
                    if (pars[i] is IEnumerable<object>)
                    {
                        List<object> enumerable = (pars[i] as IEnumerable<object>).ToList();
                        replacementStrings.Add("@" + i, String.Join(",", enumerable.Select((value, pos) => String.Format("@_{0}_{1}", i, pos))));
                        sqlParameters.AddRange(enumerable.Select((value, pos) => new SqlParameter(String.Format("@_{0}_{1}", i, pos), value ?? DBNull.Value)).ToArray());
                    }
                    else
                    {
                        sqlParameters.Add(new SqlParameter(String.Format("@{0}", i), pars[i] ?? DBNull.Value));
                    }
                }
            }
            strSql = replacementStrings.Aggregate(strSql, (current, replacementString) => current.Replace(replacementString.Key, replacementString.Value));
            using (var sqlCommand = new SqlCommand(strSql, sqlConn))
            {
                if (pars != null)
                {
                    sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
                }
                else
                {
                    //Fail-safe, just in case a user intends to pass a single null parameter
                    sqlCommand.Parameters.Add(new SqlParameter("@0", DBNull.Value));
                }
                using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
                {
                    sqlDataAdapter.Fill(ds);
                }
            }
        }
        return ds;
    }

चीयर्स।




यह संभवतः इसे करने का आधा बुरा तरीका है, मैंने इसे एक बार इस्तेमाल किया, बल्कि प्रभावी था।

आपके लक्ष्यों के आधार पर इसका उपयोग हो सकता है।

  1. एक कॉलम के साथ एक temp तालिका बनाएँ।
  2. उस कॉलम में प्रत्येक लुक-अप मान INSERT
  3. एक IN का उपयोग करने के बजाय, आप बस अपने मानक JOIN नियमों का उपयोग कर सकते हैं। (लचीलापन ++)

इसमें कुछ अतिरिक्त लचीलापन है जो आप कर सकते हैं, लेकिन यह उन परिस्थितियों के लिए अधिक अनुकूल है जहां आपके पास अच्छी अनुक्रमणिका के साथ क्वेरी करने के लिए एक बड़ी तालिका है, और आप एक से अधिक बार parametrized सूची का उपयोग करना चाहते हैं। इसे दो बार निष्पादित करने के लिए बचाता है और सभी स्वच्छता मैन्युअल रूप से की जाती है।

मैं वास्तव में कितनी तेजी से प्रोफाइलिंग करने के लिए चारों ओर नहीं मिला, लेकिन मेरी स्थिति में इसकी आवश्यकता थी।




If we have strings stored inside the IN clause with the comma(,) delimited, we can use the charindex function to get the values. If you use .NET, then you can map with SqlParameters.

DDL Script:

CREATE TABLE Tags
    ([ID] int, [Name] varchar(20))
;

INSERT INTO Tags
    ([ID], [Name])
VALUES
    (1, 'ruby'),
    (2, 'rails'),
    (3, 'scruffy'),
    (4, 'rubyonrails')
;

T-SQL:

DECLARE @Param nvarchar(max)

SET @Param = 'ruby,rails,scruffy,rubyonrails'

SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0

You can use the above statement in your .NET code and map the parameter with SqlParameter.

Fiddler demo

EDIT: Create the table called SelectedTags using the following script.

DDL Script:

Create table SelectedTags
(Name nvarchar(20));

INSERT INTO SelectedTags values ('ruby'),('rails')

T-SQL:

DECLARE @list nvarchar(max)
SELECT @list=coalesce(@list+',','')+st.Name FROM SelectedTags st

SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0



यह सकल है, लेकिन यदि आपको कम से कम एक होने की गारंटी है, तो आप यह कर सकते हैं:

SELECT ...
       ...
 WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )

आईएन ('टैग 1', 'टैग 2', 'टैग 1', 'टैग 1', 'टैग 1') को आसानी से एसक्यूएल सर्वर द्वारा अनुकूलित किया जाएगा। इसके अलावा, आपको प्रत्यक्ष अनुक्रमणिका मिलती है




यदि आपके पास SQL Server 2008 है या बाद में मैं एक तालिका मानित पैरामीटर का उपयोग करूंगा।

यदि आप SQL Server 2005 पर फंसने के लिए पर्याप्त भाग्यशाली हैं तो आप इस तरह एक CLR फ़ंक्शन जोड़ सकते हैं,

[SqlFunction(
    DataAccessKind.None,
    IsDeterministic = true,
    SystemDataAccess = SystemDataAccessKind.None,
    IsPrecise = true,
    FillRowMethodName = "SplitFillRow",
    TableDefinintion = "s NVARCHAR(MAX)"]
public static IEnumerable Split(SqlChars seperator, SqlString s)
{
    if (s.IsNull)
        return new string[0];

    return s.ToString().Split(seperator.Buffer);
}

public static void SplitFillRow(object row, out SqlString s)
{
    s = new SqlString(row.ToString());
}

आप इस तरह का उपयोग कर सकते हैं,

declare @desiredTags nvarchar(MAX);
set @desiredTags = 'ruby,rails,scruffy,rubyonrails';

select * from Tags
where Name in [dbo].[Split] (',', @desiredTags)
order by Count desc



मूल सवाल यह था कि "मैं क्वेरी को पैरामीटर कैसे करूं ..."

मुझे यहां बताएं, कि यह मूल प्रश्न का उत्तर नहीं है । अन्य अच्छे उत्तरों में पहले से ही कुछ प्रदर्शन हैं।

इसके साथ, आगे बढ़ें और इस जवाब को ध्वजांकित करें, इसे कम करें, इसे उत्तर के रूप में चिह्नित करें ... जो भी आपको विश्वास है वह सही है।

पसंदीदा ब्रैकेट से उत्तर ब्रैकेट से जवाब देखें कि मैं (और 231 अन्य) अपवित्र हूं। उनके उत्तर में दिया गया दृष्टिकोण 1) बाध्य चर के प्रभावी उपयोग के लिए अनुमति देता है, और 2) भविष्यवाणी करने के लिए कि वह योग्य हैं।

चयनित उत्तर

जो मैं यहां संबोधित करना चाहता हूं वो जोएल स्पॉल्स्की के जवाब में दिया गया दृष्टिकोण है, उत्तर "चयनित" सही जवाब के रूप में दिया गया है।

जोएल स्पॉल्स्की का दृष्टिकोण चालाक है। और यह उचित रूप से काम करता है, यह अनुमानित व्यवहार और अनुमानित प्रदर्शन प्रदर्शित करने जा रहा है, "सामान्य" मान दिए गए हैं, और मानक किनारे के मामलों जैसे कि न्यूल और खाली स्ट्रिंग के साथ। और यह एक विशेष आवेदन के लिए पर्याप्त हो सकता है।

लेकिन इस दृष्टिकोण को सामान्यीकृत करने के मामले में, आइए अधिक अस्पष्ट कोने के मामलों पर विचार करें, जैसे कि Name कॉलम में वाइल्डकार्ड वर्ण होता है (जैसा कि LIKE predicate द्वारा पहचाना जाता है।) वाइल्डकार्ड वर्ण जो मैं आमतौर पर उपयोग करता हूं वह % (एक प्रतिशत चिह्न) होता है। । तो आइए अब यहां उससे निपटें, और बाद में अन्य मामलों पर जाएं।

% चरित्र के साथ कुछ समस्याएं

'pe%ter' नाम मान पर विचार करें। (यहां उदाहरणों के लिए, मैं कॉलम नाम के स्थान पर एक शाब्दिक स्ट्रिंग मान का उपयोग करता हूं।) 'Pe% ter' के नाम मान वाले एक पंक्ति को फ़ॉर्म की क्वेरी द्वारा वापस किया जाएगा:

select ...
 where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'

लेकिन यदि खोज शब्द का क्रम उलट दिया जाता है तो वही पंक्ति वापस नहीं की जाएगी:

select ...
 where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'

जो व्यवहार हम देखते हैं वह अजीब तरह का है। सूची में खोज शब्दों के क्रम को बदलने से परिणाम सेट बदल जाता है।

यह लगभग बिना कहने के चला जाता है कि हम पेनट मक्खन से मिलान करने के लिए pe%ter नहीं चाहते हैं, इससे कोई फर्क नहीं पड़ता कि उसे कितना पसंद है।

अस्पष्ट कोने का मामला

(हां, मैं इस बात से सहमत हूं कि यह एक अस्पष्ट मामला है। शायद एक जिसे परीक्षण करने की संभावना नहीं है। हम कॉलम वैल्यू में वाइल्डकार्ड की अपेक्षा नहीं करेंगे। हम मान सकते हैं कि एप्लिकेशन इस तरह के मूल्य को संग्रहीत होने से रोकता है। लेकिन मेरे अनुभव में, मैंने शायद ही कभी डेटाबेस की बाधा देखी है जो विशेष रूप से वर्णित वर्णों या पैटर्नों को अस्वीकार करती है जिन्हें एक LIKE तुलना ऑपरेटर के दाईं ओर वाइल्डकार्ड माना जाएगा।

एक छेद पैचिंग

इस छेद को पकड़ने के लिए एक दृष्टिकोण % वाइल्डकार्ड चरित्र से बचने के लिए है। (ऑपरेटर पर एस्केप क्लॉज से परिचित किसी के लिए, यहां SQL सर्वर दस्तावेज़ों का एक लिंक है।

select ...
 where '|peanut|butter|'
  like '%|' + 'pe\%ter' + '|%' escape '\'

अब हम शाब्दिक% से मेल खा सकते हैं। बेशक, जब हमारे पास कॉलम नाम होता है, तो हमें वाइल्डकार्ड से गतिशील रूप से बचने की आवश्यकता होगी। हम % वर्ण की घटनाओं को खोजने के लिए REPLACE फ़ंक्शन का उपयोग कर सकते हैं और प्रत्येक के सामने बैकस्लैश वर्ण डाल सकते हैं, जैसे:

select ...
 where '|pe%ter|'
  like '%|' + REPLACE( 'pe%ter' ,'%','\%') + '|%' escape '\'

तो यह% वाइल्डकार्ड के साथ समस्या हल करता है। लगभग।

भागने से बचें

हम मानते हैं कि हमारे समाधान ने एक और समस्या पेश की है। बचने का चरित्र। हम देखते हैं कि हमें भी बचने वाले चरित्र की किसी भी घटना से बचने की आवश्यकता होगी। इस बार, हम इसका उपयोग करते हैं! बचने के चरित्र के रूप में:

select ...
 where '|pe%t!r|'
  like '%|' + REPLACE(REPLACE( 'pe%t!r' ,'!','!!'),'%','!%') + '|%' escape '!'

अंडरस्कोर भी

अब जब हम रोल पर हैं, हम अंडरस्कोर वाइल्डकार्ड को एक और REPLACE हैंडल जोड़ सकते हैं। और बस मस्ती के लिए, इस बार, हम भागने वाले चरित्र के रूप में $ का उपयोग करेंगे।

select ...
 where '|p_%t!r|'
  like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'

मैं भागने के लिए इस दृष्टिकोण को प्राथमिकता देता हूं क्योंकि यह ओरेकल और MySQL के साथ-साथ SQL सर्वर में भी काम करता है। (मैं आमतौर पर बचपन के चरित्र के रूप में \ बैकस्लैश का उपयोग करता हूं, क्योंकि यह वह चरित्र है जिसे हम नियमित अभिव्यक्तियों में उपयोग करते हैं। लेकिन सम्मेलन से क्यों बाध्य होना चाहिए!

उन अजीब ब्रैकेट्स

एसक्यूएल सर्वर वाइल्डकार्ड वर्णों को ब्रैकेट [] में संलग्न करके शाब्दिक के रूप में माना जा सकता है। तो हम कम से कम SQL सर्वर के लिए, अभी तक फिक्सिंग नहीं कर रहे हैं। चूंकि ब्रैकेट के जोड़े का विशेष अर्थ होता है, इसलिए हमें उनसे बचने की भी आवश्यकता होगी। यदि हम ब्रैकेट से ठीक से बचने का प्रबंधन करते हैं, तो कम से कम हमें हाइफ़न - और कैरेट ^ ब्रैकेट के भीतर परेशान नहीं होना पड़ेगा। और हम बचने वाले ब्रैकेट के अंदर कोई भी % और _ अक्षर छोड़ सकते हैं, क्योंकि हम मूल रूप से ब्रैकेट के विशेष अर्थ को अक्षम कर देंगे।

ब्रैकेट के मेल खाने वाले जोड़े ढूंढना मुश्किल नहीं होना चाहिए। यह सिंगलटन% और _ की घटनाओं को संभालने से थोड़ा मुश्किल है। (ध्यान दें कि ब्रैकेट की सभी घटनाओं से बचने के लिए पर्याप्त नहीं है, क्योंकि सिंगलटन ब्रैकेट को शाब्दिक माना जाता है, और इससे बचने की आवश्यकता नहीं होती है। तर्क अधिक मामूली हो रहा है जितना मैं अधिक टेस्ट केस चलाने के बिना संभाल सकता हूं ।)

इनलाइन अभिव्यक्ति गन्दा हो जाता है

एसक्यूएल में इनलाइन अभिव्यक्ति लंबे और उलझन में हो रही है। हम शायद इसे काम कर सकते हैं, लेकिन स्वर्ग गरीब आत्मा की मदद करता है जो पीछे आता है और इसे समझना है। एक प्रशंसक के रूप में मैं इनलाइन अभिव्यक्तियों के लिए हूं, मैं यहां एक का उपयोग नहीं कर रहा हूं, मुख्य रूप से क्योंकि मैं गड़बड़ी के कारण को समझाते हुए एक टिप्पणी छोड़ना नहीं चाहता, और इसके लिए क्षमा मांगना चाहता हूं।

एक समारोह कहाँ?

ठीक है, इसलिए, यदि हम SQL में इनलाइन अभिव्यक्ति के रूप में इसे संभाल नहीं पाते हैं, तो हमारे पास निकटतम विकल्प उपयोगकर्ता परिभाषित फ़ंक्शन है। और हम जानते हैं कि किसी चीज को गति नहीं देगा (जब तक हम उस पर एक इंडेक्स परिभाषित नहीं कर सकते, जैसे हम ओरेकल के साथ कर सकते हैं।) अगर हमें कोई फ़ंक्शन बनाना है, तो हम SQL कोड को कॉल करने वाले कोड में बेहतर कर सकते हैं बयान।

और उस कार्य में व्यवहार में कुछ अंतर हो सकते हैं, जो डीबीएमएस और संस्करण पर निर्भर है। (आपके सभी जावा डेवलपर्स के लिए एक चिल्लाओ जो किसी भी डेटाबेस इंजन को एक दूसरे के रूप में उपयोग करने में सक्षम होने के लिए उत्सुक हैं।)

डोमेन की जानकारी

हमारे पास कॉलम के लिए डोमेन का विशेष ज्ञान हो सकता है, (यानी, कॉलम के लिए लागू स्वीकार्य मानों का सेट हो सकता है। हम एक प्राथमिकता को जान सकते हैं कि कॉलम में संग्रहीत मानों में कभी भी प्रतिशत चिह्न, अंडरस्कोर या ब्रैकेट नहीं होगा जोड़े। उस मामले में, हम सिर्फ एक त्वरित टिप्पणी शामिल करते हैं कि उन मामलों को कवर किया गया है।

कॉलम में संग्रहीत मान% या _ वर्णों की अनुमति दे सकते हैं, लेकिन एक बाधा को उन मानों से बचने की आवश्यकता हो सकती है, शायद परिभाषित वर्ण का उपयोग करना, जैसे कि मान तुलना "सुरक्षित" की तरह हैं। फिर, मूल्यों के अनुमत सेट के बारे में एक त्वरित टिप्पणी, और विशेष रूप से कौन सा चरित्र एक बचने वाले चरित्र के रूप में उपयोग किया जाता है, और जोएल स्पॉल्स्की के दृष्टिकोण के साथ जाता है।

लेकिन, विशेष ज्ञान और गारंटी अनुपस्थित है, कम से कम उन अस्पष्ट कोने के मामलों को संभालने पर विचार करना महत्वपूर्ण है, और विचार करें कि व्यवहार उचित है और "विनिर्देश के अनुसार"।

अन्य मुद्दों को दोबारा शुरू किया गया

मेरा मानना ​​है कि दूसरों ने पहले ही चिंता के अन्य सामान्य क्षेत्रों में से कुछ को पर्याप्त रूप से इंगित किया है:

  • एसक्यूएल इंजेक्शन (जो उपयोगकर्ता द्वारा आपूर्ति की गई जानकारी के रूप में दिखाई देगा, और इसमें बाध्य चर के माध्यम से उन्हें आपूर्ति करने के बजाय एसक्यूएल टेक्स्ट में शामिल होगा। बाइंड चर का उपयोग करने की आवश्यकता नहीं है, यह एसक्यूएल इंजेक्शन के साथ विफल होने के लिए सिर्फ एक सुविधाजनक दृष्टिकोण है। इससे निपटने के तरीके:

  • सूचकांक की बजाय सूचकांक स्कैन का उपयोग कर अनुकूलक योजना, वाइल्डकार्ड से बचने के लिए अभिव्यक्ति या फ़ंक्शन की संभावित आवश्यकता (अभिव्यक्ति या फ़ंक्शन पर संभावित अनुक्रमणिका)

  • बाध्य चर के स्थान पर शाब्दिक मूल्यों का उपयोग स्केलेबिलिटी को प्रभावित करता है

निष्कर्ष

मुझे जोएल स्पॉल्स्की का दृष्टिकोण पसंद है। यह चालाक है। और यह काम करता है।

लेकिन जैसे ही मैंने इसे देखा, मैंने तुरंत इसके साथ एक संभावित समस्या देखी, और यह मेरी प्रकृति नहीं है कि इसे स्लाइड करें। मेरा मतलब दूसरों के प्रयासों की आलोचना करने का नहीं है। मुझे पता है कि कई डेवलपर्स अपना काम बहुत व्यक्तिगत रूप से लेते हैं, क्योंकि वे इसमें बहुत निवेश करते हैं और वे इसके बारे में बहुत अधिक परवाह करते हैं। तो कृपया समझें, यह व्यक्तिगत हमला नहीं है। जो मैं यहां पहचान रहा हूं वह समस्या का प्रकार है जो परीक्षण के बजाय उत्पादन में फसल है।

हां, मैं मूल प्रश्न से बहुत दूर चला गया हूं। लेकिन एक प्रश्न के लिए "चुने गए" उत्तर के साथ मैं एक महत्वपूर्ण मुद्दा मानने के बारे में इस नोट को कहां छोड़ सकता हूं?




Here is another alternative. Just pass a comma-delimited list as a string parameter to the stored procedure and:

CREATE PROCEDURE [dbo].[sp_myproc]
    @UnitList varchar(MAX) = '1,2,3'
AS
select column from table
where ph.UnitID in (select * from CsvToInt(@UnitList))

और समारोह:

CREATE Function [dbo].[CsvToInt] ( @Array varchar(MAX))
returns @IntTable table
(IntValue int)
AS
begin
    declare @separator char(1)
    set @separator = ','
    declare @separator_position int
    declare @array_value varchar(MAX)

    set @array = @array + ','

    while patindex('%,%' , @array) <> 0
    begin

        select @separator_position = patindex('%,%' , @array)
        select @array_value = left(@array, @separator_position - 1)

        Insert @IntTable
        Values (Cast(@array_value as int))
        select @array = stuff(@array, 1, @separator_position, '')
    end
    return
end



May be we can use XML here:

    declare @x xml
    set @x='<items>
    <item myvalue="29790" />
    <item myvalue="31250" />
    </items>
    ';
    With CTE AS (
         SELECT 
            x.item.value('@myvalue[1]', 'decimal') AS myvalue
        FROM @x.nodes('//items/item') AS x(item) )

    select * from YourTable where tableColumnName in (select myvalue from cte)



एक ही समस्या के समाधान के लिए यहां एक क्रॉस-पोस्ट है। आरक्षित डिलीमीटर से अधिक मजबूत - इसमें भागने और घोंसले वाले सरणी शामिल हैं, और एनयूएलएल और खाली सरणी को समझते हैं।

सी # और टी-एसक्यूएल स्ट्रिंग [] पैक / अनपैक उपयोगिता कार्यों

फिर आप तालिका-मूल्यवान फ़ंक्शन में शामिल हो सकते हैं।




For a variable number of arguments like this the only way I'm aware of is to either generate the SQL explicitly or do something that involves populating a temporary table with the items you want and joining against the temp table.




यदि आप .NET से कॉल कर रहे हैं, तो आप डैपर डॉट नेट का उपयोग कर सकते हैं:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = dataContext.Query<Tags>(@"
select * from Tags 
where Name in @names
order by Count desc", new {names});

यहां डैपर सोचता है, इसलिए आपको यह नहीं करना है। LINQ to SQL साथ कुछ समान है, ज़ाहिर है:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = from tag in dataContext.Tags
           where names.Contains(tag.Name)
           orderby tag.Count descending
           select tag;



I have an answer that doesn't require a UDF, XML Because IN accepts a select statement eg SELECT * FROM Test where Data IN (SELECT Value FROM TABLE)

You really only need a way to convert the string into a table.

This can be done with a recursive CTE, or a query with a number table (or Master..spt_value)

Here's the CTE version.

DECLARE @InputString varchar(8000) = 'ruby,rails,scruffy,rubyonrails'

SELECT @InputString = @InputString + ','

;WITH RecursiveCSV(x,y) 
AS 
(
    SELECT 
        x = SUBSTRING(@InputString,0,CHARINDEX(',',@InputString,0)),
        y = SUBSTRING(@InputString,CHARINDEX(',',@InputString,0)+1,LEN(@InputString))
    UNION ALL
    SELECT 
        x = SUBSTRING(y,0,CHARINDEX(',',y,0)),
        y = SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y))
    FROM 
        RecursiveCSV 
    WHERE
        SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y)) <> '' OR 
        SUBSTRING(y,0,CHARINDEX(',',y,0)) <> ''
)
SELECT
    * 
FROM 
    Tags
WHERE 
    Name IN (select x FROM RecursiveCSV)
OPTION (MAXRECURSION 32767);



आप प्रत्येक मान को पैरामीटर कर सकते हैं, इसलिए कुछ ऐसा है:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";

string[] paramNames = tags.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
}

जो आपको देगा:

cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"

नहीं, यह एसक्यूएल इंजेक्शन के लिए खुला नहीं है। कमांडटेक्स्ट में एकमात्र इंजेक्शन टेक्स्ट उपयोगकर्ता इनपुट पर आधारित नहीं है। यह पूरी तरह से हार्डकोडेड "@tag" उपसर्ग, और एक सरणी के सूचकांक पर आधारित है। सूचकांक हमेशा एक पूर्णांक होगा, उपयोगकर्ता उत्पन्न नहीं होता है, और सुरक्षित है।

उपयोगकर्ता द्वारा इनपुट किए गए मान अभी भी पैरामीटर में भरे हुए हैं, इसलिए वहां कोई भेद्यता नहीं है।

संपादित करें:

इंजेक्शन चिंताएं अलग-अलग हैं, ध्यान दें कि पैरामीटर की चरम संख्या (ऊपर के रूप में) को निष्क्रिय करने के लिए कमांड टेक्स्ट का निर्माण करना, कैद किए गए प्रश्नों का लाभ उठाने के लिए एसक्यूएल सर्वर की क्षमता को बाधित करता है। शुद्ध परिणाम यह है कि आप लगभग पहले स्थान पर पैरामीटर का उपयोग करने के मूल्य को निश्चित रूप से खो देते हैं (जैसा कि केवल एसक्यूएल में अनुमानित तारों को डालने का विरोध करता है)।

नहीं कि कैश की गई क्वेरी योजनाएं मूल्यवान नहीं हैं, लेकिन आईएमओ इस क्वेरी से काफी लाभ देखने के लिए लगभग जटिल नहीं है। जबकि संकलन लागत निष्पादन लागत (या उससे भी अधिक) तक पहुंच सकती है, फिर भी आप मिलीसेकंड की बात कर रहे हैं।

यदि आपके पास पर्याप्त रैम है, तो मुझे उम्मीद है कि SQL सर्वर शायद पैरामीटर की सामान्य गणना के लिए भी एक योजना को कैश करेगा। मुझे लगता है कि आप हमेशा पांच पैरामीटर जोड़ सकते हैं, और अनिर्दिष्ट टैग नल हो सकते हैं - क्वेरी प्लान एक जैसा होना चाहिए, लेकिन यह मेरे लिए बहुत बदसूरत लगता है और मुझे यकीन नहीं है कि यह माइक्रो-ऑप्टिमाइज़ेशन के लायक होगा (हालांकि, स्टैक ओवरफ़्लो पर - यह बहुत लायक हो सकता है)।

साथ ही, SQL सर्वर 7 और बाद में प्रश्नों को स्वत: पैरामीटर कर देगा, इसलिए मानकों का उपयोग करना प्रदर्शन दृष्टिकोण से वास्तव में आवश्यक नहीं है - हालांकि, यह सुरक्षा दृष्टिकोण से महत्वपूर्ण है - विशेष रूप से इस तरह के उपयोगकर्ता इनपुट डेटा के साथ।




यह भी काम करना चाहिए, शायद इसे आज़माएं।

  Select * from Users a
            where EXISTS (Select * from Users b 
                where (     a.name = b.name 
                        OR  a.email = b.email)
                     and a.ID != b.id)

विशेष रूप से आपके मामले में अच्छा यदि आप डुप्लिकेट की खोज करते हैं जिनके पास कुछ प्रकार का उपसर्ग या सामान्य परिवर्तन है जैसे कि मेल में नया डोमेन। तो आप इन कॉलम पर प्रतिस्थापन () का उपयोग कर सकते हैं





sql sql-server parameters