CODEWITHSUNDEEP
sqlsql-servert-sql
Edwin Aquino
Edwin Aquino

Reputation: 219

Query SQL with similar values

I have to make a query to a base using as a comparison a string like this 12345678, but the value to compare is this way12.345.678, if I do the following query it does not return anything.

SELECT * FROM TABLA WHERE CAMPO = '12345678'

Where CAMPO would have the value of (12.345.678), if I replace = with a like, it does not return the data either

SELECT * FROM TABLA WHERE CAMPO like '12345678%'
SELECT * FROM TABLA WHERE CAMPO like '%12345678'
SELECT * FROM TABLA WHERE CAMPO like '%12345678%'

None of the 3 previous consultations works for me, how can I make this query?

The value can be of either 7, 8 or 9 numbers and the. It has to be every 3 from the end to the beginning

Upvotes: 0

Views: 109

Answers (5)

Christoph
Christoph

Reputation: 3642

If the table containing IDs like 12.345.678 is big (contains many records), I would add a computed field that removes the dots (and if this ID does never contain any alphanumeric characters other than dots and has no significant leading zeros then also cast it in an INT or BIGINT) and persist it and lay an index over it. That way you loose a little time when inserting the record but are querying it with maximum speed and therefore saving processor power.

Upvotes: 0

HABO
HABO

Reputation: 15816

A SARGABLE solution would be to write a function that takes your target value ('12345678') and inserts the separators ('.') every third character from right to left. The result ('12.345.678') can then be used in a where clause and benefit from an index on CAMPO.

The following code demonstrates an approach without creating a user-defined function (UDF). Instead, a recursive common table expression (CTE) is used to process the input string three characters at a time to build the dotted target string. The result is used in a query against a sample table.

To see the results from the recursive CTE replace the final select statement with the commented select immediately above it.

-- Sample data.
declare @Samples as Table ( SampleId Int Identity, DottedDigits VarChar(20) );
insert into @Samples ( DottedDigits ) values
  ( '1' ), ( '12' ), ( '123' ), ( '1.234' ), ( '12.345' ),
  ( '123.456' ), ( '1.234.567' ), ( '12.345.678' ), ( '123.456.789' );
select * from @Samples;

-- Query the data.
declare @Target as VarChar(15) = '12345678';

with
  Target as (
    -- Get the first group of up to three characters from the tail of the string ...
    select
      Cast( Right( @Target, 3 ) as VarChar(20) ) as TargetString,
      Cast( Left( @Target, case when Len( @Target ) > 3 then Len( @Target ) - 3 else 0 end ) as VarChar(20) ) as Remainder
    union all
    -- ... and concatenate the next group with a dot in between.
    select
      Cast( Right( Remainder, 3 ) + '.' + TargetString as VarChar(20) ),
      Cast( Left( Remainder, case when Len( Remainder ) > 3 then Len( Remainder ) - 3 else 0 end ) as VarChar(20) )
      from Target
      where Remainder != ''
  )
  -- To see the intermediate results replace the final   select   with the line commented out below:
  --select TargetString from Target;
  select SampleId, DottedDigits
    from @Samples
    where DottedDigits = ( select TargetString from Target where Remainder = '' );

An alternative approach would be to add a indexed computed column to the table that contains Replace( CAMPO, '.', '' ).

Upvotes: 0

Alexandre Calvario
Alexandre Calvario

Reputation: 153

I recommend you to convert the number to numeric

So you can use < and > operators and all functions that require you to have a number...

the best way to achieve this is to make sure you remove any unecessary dots and convert the commas to dots. like this

CONVERT(NUMERIC(10, 2), 
               REPLACE(
                       REPLACE('7.000,45', '.', ''),
                       ',', '.'
                      )
              )

I hope this will help you out.

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

Use REPLACE() function to replace all the dots '.' as

SELECT *
FROM(
     VALUES ('12.345.678'),
            ('23.456.789')
    ) T(CAMPO)
WHERE REPLACE(CAMPO, '.', '') = '12345678';

Your query should be

SELECT * FROM TABLA WHERE REPLACE(CAMPO, '.', '') = '12345678';

Upvotes: 3

Tab Alleman
Tab Alleman

Reputation: 31775

You can compare the string without the dots to a REPLACE(StringWithDots, '.','')

Upvotes: 3

Related Questions