Change string to min/max values to generate middle value as a result

Question

EDIT: The field I was trying to edit, I realized is nvarchar(max), and contains a TON of garbage data, and the extensions I'm trying to get to work are in a single role as comma separated values...it would be quite improbable to do what I am requesting. A HUGE thank you to all those that helped!

Before I ask, know that I have read-only access to the DB and cannot write to temp tables...as that was one solution I thought of...

I am using the following query currently:

USE Database
GO
DECLARE @dn VARCHAR(13)
SET @dn = '%15555555555%'
SELECT switch_extension, line_number, system_configurations.name, system_configurations.description, lines.system_id
FROM lines, system_configurations
WHERE lines.system_id=system_configurations.system_id
AND switch_extension like @dn
AND lines.status = 1

SELECT distinct CS.name, CTD.cti_data_value
FROM cti_source AS CS with (nolock)
  JOIN cti_source_data AS CTD with (nolock)ON CS.cti_source_id = CTD.cti_source_id
--WHERE CS.status = 1 
    AND (CTD.cti_data_name = 'Agent_Ext'or CTD.cti_data_name = 'IVR_Ext') 
    AND CTD.cti_data_value LIKE @dn

The problem is, CTD.cti_data_value can contain ranges stored as strings instead of a new row with individual values, such as '15551112222-15551113333' instead of 15551112222, 15551112223, ...

If I query for a @dn in this range (unless the start or end of the range), I get no results (in the example above, if I searched for 15551112345, there would be no results, even though it exists in the "range").

Is there a way to change this to get min/max in this single string, then find a result between the min/max, or am I up a creek?

score 0 · Answer 1 · answered Feb 21 '17 at 17:33

0

You need to split the values in CTD.cti_data_value into lower and upper values of a range. Change this line in your where clause...

AND CTD.cti_data_value LIKE @dn

to this...

AND @dn BETWEEN
    CASE WHEN  CHARINDEX('-', CTD.cti_data_value) > 0 THEN
        SUBSTRING(CTD.cti_data_value, 1,  CHARINDEX('-', CTD.cti_data_value) - 1)
    ELSE
        CTD.cti_data_value
    END 
  AND 
    CASE WHEN  CHARINDEX('-', CTD.cti_data_value) > 0 THEN
        SUBSTRING(CTD.cti_data_value, CHARINDEX('-', CTD.cti_data_value) + 1, LEN(CTD.cti_data_value) - CHARINDEX('-', CTD.cti_data_value))
    ELSE
        CTD.cti_data_value
    END

This will accommodate having a range of values (e.g. X-Y) or just a singular value. I must warn you that comparing numerical values in string variables (e.g. CHAR, VARCHAR, etc.) could lead to unpredictable results. For instance, when comparing numerical values in string variables "2" is greater than "10".

If you are ABSOLUTELY sure that the numbers in CTD.cti_data_value will always be the same number of digits you will be fine. If that is not the case, you should convert your numerical values to INT (or whatever is appropriate for your situation) like this...

AND CONVERT(INT, @dn) BETWEEN
    CONVERT(INT, CASE WHEN  CHARINDEX('-', CTD.cti_data_value) > 0 THEN
        SUBSTRING(CTD.cti_data_value, 1,  CHARINDEX('-', CTD.cti_data_value) - 1)
    ELSE
        CTD.cti_data_value
    END)
  AND 
    CONVERT(INT, CASE WHEN  CHARINDEX('-', CTD.cti_data_value) > 0 THEN
        SUBSTRING(CTD.cti_data_value, CHARINDEX('-', CTD.cti_data_value) + 1, LEN(CTD.cti_data_value) - CHARINDEX('-', CTD.cti_data_value))
    ELSE
        CTD.cti_data_value
    END)

answered Feb 21 '17 at 17:33

Isaac

3,240
2
24
31

The number of digits will vary. They could be single entries of a length of 8 or 11 characters, or if it's a range, it will be 17 or 23 (including the '-'). I attempted to use the second solution given, but it gives an error "Error converting data type varchar to int" (I then attempted bigint, but that didn't work either) – Ellery Higgins Feb 21 '17 at 17:41
Is there a non-numeric character in the value you are trying to convert? – Isaac Feb 21 '17 at 17:58
It had a % as wildcard, but I even removed that to attempt. http://pastebin.com/15d5q9DJ is the example, and returns "Msg 8114, Level 16, State 5, Line 10 Error converting data type nvarchar to bigint." – Ellery Higgins Feb 21 '17 at 18:01
What is the datatype and size of CTD.cti_data_value? – Isaac Feb 21 '17 at 18:24
It's an extension/phone number or range of them. Datatype is nvarchar(60). This can be '11081111', '15551112222', '11081111-11081114', '15551112222-15551112223' as examples. This would make sense why a conversion wouldn't work, as the '-' can be contained in the string and is non-numeric. The previous version of the software had one extension/phone number per row, and results would turn up if it existed. This one stores it as a string, and unless you're lucky enough to have the first/last number in a range, it shows as non-existent without using the front end to manually find it. – Ellery Higgins Feb 21 '17 at 18:39
Edit above, it's actually nvarchar(max). – Ellery Higgins Feb 21 '17 at 18:49
In my answer I wasn't converting the entire field, just the portion before or after the dash. Not sure what the issue is. – Isaac Feb 21 '17 at 18:50

score 0 · Answer 2 · edited May 23 '17 at 10:29

I think you would need to split the string into different components. See Splitting the string in sql server

Now that is a read/write version but you are doing something similar. And then your minimum will always be the bit before the delimiter and your max will always be the bit after.

A second option would be to write a user-defined function in .net that would take in the string, do the sanity checking, and return the appropriate values you are looking for. The use of the CLR if performance is critical might actually be a good thing here.

But without the CLR, you need to:

find the location of the delimiter (- in this case)
Take the string up to, but not including the delimiter as the min, and
Take the string following the delimiter as the max.

score 0 · Answer 3 · answered Feb 21 '17 at 17:46

You could make use of CTE instead of a table and calculate the Min and Max values in your ranges by splitting them, and checking if your @dn is between those values. Make sure @dn is declared as BIGINT. I am assuming that your values are always BIGINT (your values are over INT).

Here's a working example to prove the point:

    DECLARE @dn BIGINT  = 15555555555;
    --SET       @dn         = 15551112223;

    WITH cti_source_data AS (
        SELECT  *
        FROM    (VALUES ('15555555555'), ('15551112222-15551113333')) AS V (cti_data_value)

    ), CTD AS (
        SELECT  *, 
                CASE CHARINDEX('-', cti_data_value) WHEN 0 THEN cti_data_value ELSE SUBSTRING(cti_data_value, 0, CHARINDEX('-', cti_data_value)) END MinValue,
                SUBSTRING(cti_data_value, CHARINDEX('-', cti_data_value)+1, LEN(cti_data_value)) MaxValue
        FROM    cti_source_data
    )
    SELECT * 
    FROM    CTD
    WHERE   @dn BETWEEN MinValue AND MaxValue

As for your queries, you would need to replace the tables with CTEs calculating these Min and Max values:

    DECLARE @dn BIGINT  = 15555555555;

    SELECT switch_extension, line_number, system_configurations.name, system_configurations.description, lines.system_id
    FROM lines, system_configurations
    WHERE lines.system_id=system_configurations.system_id
    AND switch_extension like '%' + @dn '%'
    AND lines.status = 1

    ;WITH CTD AS (
        SELECT  *, 
                CASE CHARINDEX('-', cti_data_value) WHEN 0 THEN cti_data_value ELSE SUBSTRING(cti_data_value, 0, CHARINDEX('-', cti_data_value)) END MinValue,
                SUBSTRING(cti_data_value, CHARINDEX('-', cti_data_value)+1, LEN(cti_data_value)) MaxValue
        FROM    cti_source_data (NOLOCK)
    )
    SELECT distinct CS.name, CTD.cti_data_value
    FROM cti_source AS CS with (nolock)
      JOIN ON CS.cti_source_id = CTD.cti_source_id
    --WHERE CS.status = 1 
        AND (CTD.cti_data_name = 'Agent_Ext'or CTD.cti_data_name = 'IVR_Ext') 
        --AND CTD.cti_data_value LIKE @dn
        AND @dn BETWEEN CTD.MinValue AND CTD.MaxValue

Change string to min/max values to generate middle value as a result

3 Answers3