SQL Syntax

Question

SQL Syntax

kabaari

SSCrazy

Points: 2235
More actions
April 21, 2010 at 10:00 am

#223726

Hi All,
I'm looking for the correct syntax to find my workorder number within my serial number which starts at the 7th character and is 5 characters long. I even tried "(_)" but that didn't work. Any help is appreciated.
select sn, unit
from table
where sn like '%#FORM.Workorder#%'

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

It would help if you would post the DDL (CREATE TABLE statement) for the table, sample data (series of INSERT INTO table statements) for the table, and expected results. Hard to help diagnosis a problem when we have nothing to work with ourselves.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Buddy, as Lynn said, it really is hard to guess and tell u a solution.. so post ur sample records, ur table structure and desired output..

But for starters, i will u this code.. if u need to get a subset of characters from a position , use SUBSTRING.

so your code now will look like the following:

select sn, unit

from table

where SUBSTRING(sn,7,5) like <your search condition>

Hope this gets u started :-)! but before that, how about you reading the first link on Lynn Pettis signature line to get ur "questioning" skills honed? 😎

Cheers!!

kabaari SSCrazy Points: 2235 More actions · Answer 3

Coldcoffee,

Thanks. Not sure if posting data would apply in this situation. But, I posted data in the past and ended up with people responding with how valuable their time was because I posted the data incorrectly. SUBSTRING is what I was looking for.

Thanks again,

djkhalif

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

kabaari (4/21/2010)
Coldcoffee,
Thanks. Not sure if posting data would apply in this situation. But, I posted data in the past and ended up with people responding with how valuable their time was because I posted the data incorrectly. SUBSTRING is what I was looking for.
Thanks again,
djkhalif

Please note that if you write the query the way Coldcoffee showed you it will not take advantage of any indexes that may exist on your table.

Also, take the time to read the first article I reference in my signature block. It shows you how to post data in a readily consummable format that makes it easy for us to load a table with sample data.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

kabaari (4/21/2010)
Coldcoffee,
Thanks. Not sure if posting data would apply in this situation. But, I posted data in the past and ended up with people responding with how valuable their time was because I posted the data incorrectly. SUBSTRING is what I was looking for.
Thanks again,
djkhalif

Heh... it's your question and you're the one that needs an answer. Read and heed the link that Lynn referred you to and life will become a pleasure again. 😉

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

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 6

I'll second (third?) the sentiment of Lynn and Jeff, from the perspective of a non-expert, if you post the code, you're taking advantage of their experience and brain-power, and they may come up with a solution that you never would have considered because you didn't know it existed.

Worth the effort.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

Lynn Pettis (4/21/2010)
Please note that if you write the query the way Coldcoffee showed you it will not take advantage of any indexes that may exist on your table.

Guess am under the hammer for providing cant-use-index code :crying:!! Substring does an index scan i guess (am not into indexes much :() so it is not am optimal code.. Lynn/Jeff, can u point me on how to scale Substring function , using this example??

WayneS SSC Guru Points: 95460 More actions · Answer 8

ColdCoffee (4/21/2010)
Lynn Pettis (4/21/2010)
Please note that if you write the query the way Coldcoffee showed you it will not take advantage of any indexes that may exist on your table.
Guess am under the hammer for providing cant-use-index code :crying:!! Substring does an index scan i guess (am not into indexes much :() so it is not am optimal code.. Lynn/Jeff, can u point me on how to scale Substring function , using this example??

As stated, SubString won't use an index. What you need to do is use LIKE, with the underscore wildcard at the beginning.

This code works real nice:

-- See how you start off by actually creating a table and then

-- inserting the data into it? Your doing this makes it a lot easier

-- for all of us volunteers to help you. So, help us help you.

-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

IF object_id('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp

CREATE TABLE #Temp (SerialNumber varchar(20) PRIMARY KEY CLUSTERED)

-- Create a virtual tally table.

-- See Jeff Modem's article The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

-- at http://www.sqlservercentral.com/articles/T-SQL/62867/ for how a tally table can split strings apart.

;WITH

TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),

MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),

TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)

INSERT INTO #Temp

SELECT TOP (99999) CHAR(ABS(CHECKSUM(NEWID()))%26+65) + -- random characters

CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

right('00000' + convert(varchar(5),N),5) +

CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

CHAR(ABS(CHECKSUM(NEWID()))%26+65)

FROM TALLY

declare @NumToCheck smallint

set @NumToCheck = 9458

select SerialNumber

from #Temp

where SerialNumber like '______' + right('00000' + convert(varchar(5), @NumToCheck), 5) + '%'

It uses an index seek, as shown by this execution plan:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2