NEED Query to find apostrophes in table

I've tried everything I can think of to find all the records in a table column (lastname) that contain an apostrophe. I know they are there (O'Brian, D'Marcus, etc.) However, I keep getting syntax errors.
Could someone PLEASE help?!!
Thanks,
KarenSELECT *
FROM theTableWithTheUnspecifiedName
WHERE lastName LIKE '%''%'-PatP|||Either way

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 varchar(80))
CREATE INDEX IX1 ON myTable99(Col1)
GO

INSERT INTO myTable99(Col1)
SELECT 'abc' UNION ALL
SELECT 'a''c' UNION ALL
SELECT 'xyz'
GO

SELECT *
FROM myTable99
WHERE LEN(Col1) <> LEN(REPLACE(Col1,CHAR(39),''))
GO

SELECT *
FROM myTable99
WHERE Col1 LIKE '%''%'
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

...it's a scan. I wonder if one would be more effecient than the other though|||Works fine.

Many thanks!!!!!

Karen

Need help with update/select statements please.

Hi all,
I have the following data and need to clean up all the junk data and keep
the valid Lastname and FirstName. Please
see the result want below. Thank you very much in advance.
IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO
CREATE TABLE #Temp
(
LastName VARCHAR(25) NULL,
FirstName VARCHAR(25) NULL
)
GO
INSERT #Temp VALUES ('##Jairdullo', '##Mary')
INSERT #Temp VALUES ('(6957)BARAHONA', '(6957)MARIA')
INSERT #Temp VALUES ('(ds)Ah', '(ds)Sati')
INSERT #Temp VALUES ('(ds)BURNS', '(ds)CHRISTA')
INSERT #Temp VALUES ('*ALCARAZ', '*MICHAEL')
INSERT #Temp VALUES ('*CERRATO 95066454', '*GLENDA')
INSERT #Temp VALUES ('.', '23CURT')
INSERT #Temp VALUES ('-GREENE', '34DARLENE')
INSERT #Temp VALUES ('/', '?John')
INSERT #Temp VALUES ('~~~FREEMAN', '@.@.@.ANDREW')
INSERT #Temp VALUES ('123Zargarian', '97Lisa')
INSERT #Temp VALUES ('12DE LA CRUZ', 'SANDRA')
INSERT #Temp VALUES ('6957/ Wolfe', '6957/ Donald')
INSERT #Temp VALUES ('!ABBY', 'ABBY')
INSERT #Temp VALUES ('@.ABBOUD FAOUR', 'PARIS')
INSERT #Temp VALUES ('#ABBOTT', '$MONICA')
INSERT #Temp VALUES ('%ABBENHUYS', '^Abbath')
INSERT #Temp VALUES ('&AAMODT', '(MARILYN')
INSERT #Temp VALUES (')%aaland', '-8052160336')
INSERT #Temp VALUES ('a', 'Ksd')
INSERT #Temp VALUES ('ABD-EL-SHAID', 'DELIA')
INSERT #Temp VALUES ('12HARRISON-PEREZ', '@.#3CHRISTINA')
go
SELECT *
FROM #Temp
go
LastName FirstName
-- --
##Jairdullo ##Mary
(6957)BARAHONA (6957)MARIA
(ds)Ah (ds)Sati
(ds)BURNS (ds)CHRISTA
*ALCARAZ *MICHAEL
*CERRATO 95066454 *GLENDA
. 23CURT
-GREENE 34DARLENE
/ ?John
~~~FREEMAN @.@.@.ANDREW
123Zargarian 97Lisa
12DE LA CRUZ SANDRA
6957/ Wolfe 6957/ Donald
!ABBY ABBY
@.ABBOUD FAOUR PARIS
#ABBOTT $MONICA
%ABBENHUYS ^Abbath
&AAMODT (MARILYN
)%aaland -8052160336
a Ksd
ABD-EL-SHAID DELIA
12HARRISON-PEREZ @.#3CHRISTINA
Result want:
--Note: Remove hypen between the Lastname ABD-EL-SHAID with ABDELSHAID
LastName FirstName
-- --
Jairdullo Mary
BARAHONA MARIA
Ah Sati
BURNS CHRISTA
ALCARAZ MICHAEL
CERRATO GLENDA
GREENE DARLENE
FREEMAN ANDREW
Zargarian Lisa
LACRUZ SANDRA
Wolfe Donald
ABBY ABBY
ABBOUDFAOUR PARIS
ABBOTT MONICA
ABDELSHAID DELIA
12HARRISONPEREZ CHRISTINAUPDATE Foobar
SET last_name
= REPLACE( REPLACE( ..
REPLACE(last_name, '-', '')(
'#','')
..);
first_name
= REPLACE( REPLACE( ..
REPLACE(last_name, '-', '')(
'#','')
..);
You can nest the REPLACE() funciton 32 levels deep. This avoids cursors
and other non-relational code.