like operator problem

Question

Post reply

like operator problem

polo.csit

SSCrazy

Points: 2574
More actions
January 3, 2009 at 5:20 am

#97031

Hai all
my table data is like
values
30%
6000
20%
3
0.5%
2%
0%
3
0%
0%
1
1
but i want only values is
values
30%
20%
0.5%
2%
0%
0%
0%

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29384 More actions · Answer 1

You could try something like

ColumnName LIKE '%[%]'

The square brackets around the second percent sign mean that this one should not be used as the wildcard. Hence, this condition will find strings that end with the percent sign

Have a look in Books Online for more info about the LIKE operator

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

WHERE RIGHT(column, 1) = '%'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

ColumnName LIKE '%/%' ESCAPE '/'

With a character defined as an escape character, any character that follows that in the LIKE will be treated as a literal value.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Now that you have several different methods for separating the data, my recommendation would be to normalize the table so you don't have to use any of those methods in the future. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

msimone SSCommitted Points: 1842 More actions · Answer 5

Hello.

I have a machine with sqlserver2008 developer edition with sp1, and i have other machine with enterprise edition with sp1.

The BD's have the same table with the same information.

In developer edition, a select with like operator the function is good, but in developer edition, is bad, less rows.

The column is a nvarchar(max), but if the column is varchar(max), the results are same.

Please, i need help.

Thanks.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Please post your question in a new thread and include details such as the query, table definition and index definitions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

msimone SSCommitted Points: 1842 More actions · Answer 7

GilaMonster:

CREATE TABLE [IPA].[BDVersion](

[PkBDVersion] [int] NOT NULL,

[PkNumeroLote] [int] NOT NULL,

[PkNumMicroLote] [int] NOT NULL,

[AccionLote] [nvarchar](2) NOT NULL,

[TipoObjetoLote] [nvarchar](3) NOT NULL,

[EsquemaObjeto] [nvarchar](5) NOT NULL,

[Objeto] [nvarchar](128) NOT NULL,

[AccionMicroLote] [nvarchar](2) NOT NULL,

[TipoObjetoMicroLote] [nvarchar](3) NOT NULL,

[EsquemaObjetoML] [nvarchar](5) NOT NULL,

[ObjetoML] [nvarchar](128) NOT NULL,

[FechaEntrada] [datetime] NOT NULL,

[FechaActualizacion] [datetime] NULL,

[FechaEnvio] [datetime] NULL,

[Codigo] [nvarchar](max) NULL,

[FechaBaja] [datetime] NULL,

[Condicion] [tinyint] NULL,

CONSTRAINT [Pk_BDVersion] PRIMARY KEY CLUSTERED

(

[PkBDVersion] ASC,

[PkNumeroLote] ASC,

[PkNumMicroLote] ASC

)

) ON [PRIMARY]

The select :

select pknummicrolote

from IPA.bdversion

where codigo like '%CreaBD%'

Thanks.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

msimone (7/7/2010)
In [font="Arial Black"]developer edition[/font], a select with like operator the function is good, but in [font="Arial Black"]developer edition[/font], is bad, less rows.

???? :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions