HI simple query only

Question

Post reply

HI simple query only

brix75brix

SSC Rookie

Points: 28
More actions
March 6, 2008 at 3:49 am

#183461

Hi can anyone help me for this query
I have a column i a table that should contain only 12 characters but there are less than 12 so how can select the those records which are less than 12 characters?
another:
I have this column
col1
1 1= login
2 2= logout
1
2 the proper behaviour should be alternate 1 & 2
2
1
2
Is there a script that will generate the rows that duplicates?

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

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

Giri Duddu SSCommitted Points: 1638 More actions · Answer 1

u can try using below syntax in where clause

where len(colname) < 12

ksullivan SSCrazy Points: 2346 More actions · Answer 2

for your second question, the answer depends a lot on what other columns are in you table besides the 1s and 2s. How do you identify the sequence?

This code works only for a really simple, easily spotted sequence:

create table #temp (lo int, dt datetime)

insert into #temp

select 1, '2008-03-01' union

select 2, '2008-03-02' union

select 1, '2008-03-03' union

select 2, '2008-03-04' union

select 2, '2008-03-05' union

select 1, '2008-03-06' union

select 2, '2008-03-07' --union

select *

from #temp a

join #temp b

on a.lo = b.lo and a.dt = b.dt - 1

There are several better solutions elsewhere on this site.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Well, here is a way to do it without functions:

WHERE Not (column LIKE '____________%')

...bored...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

Do what, Barry? Maybe I'm tired but I don't get what you're solving... please explain.

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

(Oops, got it backwards. Fixed now...)

This is a substitute for [font="Courier New"]WHERE Len(..) < 12[/font]

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

No... I meant the LIKE thingy you wrote... but, I get it now... and, yes, you must've been bored 😛

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

yeah...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]