How do I search a string for a single space and not return double spaces e.g. find 'A B' not 'A B'

Question

How do I search a string for a single space and not return double spaces e.g. find 'A B' not 'A B'

olibbhq

SSCarpal Tunnel

Points: 4034
More actions
November 25, 2011 at 3:09 am

#248547

Hopefully a simple question for friday morning, but I have no clue in SQL how to do this,
Many Thanks for the help,
Oliver

Viewing 4 posts - 1 through 3 (of 3 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

oliver.morris (11/25/2011)
Hopefully a simple question for friday morning, but I have no clue in SQL how to do this,
Many Thanks for the help,
Oliver

Something like this?

DECLARE @TABLE AS TABLE (string VARCHAR(50))

INSERT INTO @TABLE

SELECT 'A B' --Good

UNION ALL SELECT 'AB CDEFGH' --Good

UNION ALL SELECT 'A BC' --Bad

UNION ALL SELECT ' ABC' --Good

UNION ALL SELECT ' ABC' --Bad

SELECT string

FROM @TABLE

WHERE PATINDEX(' [^ ]%',string) > 0 OR PATINDEX('%[^ ] [^ ]%',string) > 0

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 2

spot on, this is exactly it, didnt think to use patindex.

Many Thanks for your help,

Oliver

ScottPletcher SSC Guru Points: 101099 More actions · Answer 3

You don't actually need PATINDEX for that, LIKE will work just as well:

WHERE string LIKE ' [^ ]%' OR string LIKE '%[^ ] [^ ]%'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".