Using Like Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using Like Query

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2009-06-25 : 07:11:57

Hi

I am trying to select rows from a table where a specified field has the first 3 characters as empty string and the fourth character is a T. But I am getting back all rows where field is "ABCT" and " T". I only wnat to return the second " T". I was using a substring(fieldName, 4,1) to do this but not sure how to only include the ones with spaces at the start?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 07:17:18

WHERE Col1 LIKE SPACE(3) + 'T%'
WHERE SUBSTRING(Col1, 1, 4) = SPACE(3) + 'T'

E 12°55'05.63"
N 56°04'39.26"

Looper
Yak Posting Veteran

68 Posts

Posted - 2009-06-25 : 07:44:33

Thanks

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-25 : 08:02:45

Peso, one could straight away use this too .. like ' T%', i mean if someone doesn't know of Space function in SQL server... Wat say??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 08:04:32

I tried that first, but this forum screws up the string so that the three spaces look like one.

E 12°55'05.63"
N 56°04'39.26"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-25 : 14:47:52

I'm not saying it's better, but you can also use square brackets to denote a space:

WHERE Col1 LIKE '[ ][ ][ ]T%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-26 : 05:51:47

[code]
WHERE SUBSTRING(Col1, 1, 4) = ' '+ 'T'
[/code]

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-26 : 05:52:22

quote:
Originally posted by Peso

I tried that first, but this forum screws up the string so that the three spaces look like one.

E 12°55'05.63"
N 56°04'39.26"

Forgot code tag?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 06:15:50

Busted!

E 12°55'05.63"
N 56°04'39.26"

saran_d28
Starting Member

36 Posts

Posted - 2009-06-26 : 06:18:29

Hi,

WHERE RTRIM(COL1) LIKE 'T%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 06:19:54

You mean LTRIM() ?

Even that will not guarantee that there were 3 leading spaces. 2 leading spaces, 1 leading space and even no leading space will satisfy the LIKE condition.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources