Showing only rows with a fixed length of number-CodePudding

I would like to only showing rows with a fixed length

I have several rows with client's number, and by the national length all the telephones number must only have 11 characters, but I have some data with 8 characters, or even with only zeros.

And I need to show, only those rows with 11 characters in it, and don't show the rest of the data

Like this:

My code would be like this:

SELECT
  Codclie
, Descrip AS [Name]
, SUBSTRING(Telef,1,11) AS [Telephone] 
, CAST(FechaE AS DATE) AS [first sale]
FROM
WHERE
    Telef <> ''
    AND
    Telef NOT LIKE ('00%')
ORDER BY
CAST(FechaE AS DATE) DESC

CodePudding user response：

An underscore _ matches a single character when used with string operator/function such as like or patindex. To make sure the phone numbers must start with a number between 1-9 followed by exactly 10 more characters, you could do

where patindex('[1-9]_________',telef) = 1

If you have non-numeric characters, then you might want to add another condition to make you're filtering for numbers only

and try_cast(telef as int) is not null

CodePudding user response：

You could use a temp table

SELECT
  Codclie
, Descrip AS [Name]
, SUBSTRING(Telef,1,11) AS [Telephone] 
, CAST(FechaE AS DATE) AS [first sale]
into #temp
FROM
WHERE
    Telef <> ''
    AND
    Telef NOT LIKE ('00%')
ORDER BY
CAST(FechaE AS DATE) DESC

Then delete whatever you want from the temp table

DELETE FROM #temp WHERE LEN(Telephone) > 7

Then simply select * from #temp