April 3, 2023 at 4:56 pm
Hi,
I am trying to do Excel style "contains" in SQL, but doesn't work.
There are two forename fields in my two tables.
I would like to know if first forename is "contained" in second forename field or vice-versa. The name can be anywhere in the other column. Basically identifying individuals who put in their middle name or surname with forenames separated by a space.
I tried using like '%p.forenames%' - but I have a feeling it's taking it literally as a string.
Thanks.
V
April 3, 2023 at 5:06 pm
try:
like '%' + p.forenames + '%'
April 3, 2023 at 5:21 pm
Okay I am not understanding your comment "I have a feeling it is taking it literally as a string" -- how else should it take it? I mean you are doing a string comparison yes/no? Can you please explain your concern a bit better.
As an option you could use split_string( ) and split it on the " " and then check each individual piece but I do not feel that should be necessary if you fully understand how the LIKE statement works and I am fully understanding what you are trying to do.
April 3, 2023 at 5:31 pm
Okay I am not understanding your comment "I have a feeling it is taking it literally as a string" -- how else should it take it? I mean you are doing a string comparison yes/no? Can you please explain your concern a bit better.
As an option you could use split_string( ) and split it on the " " and then check each individual piece but I do not feel that should be necessary if you fully understand how the LIKE statement works and I am fully understanding what you are trying to do.
The OP was using like '%p.forenames%' this would look for a string with p.forenames in it.
For example, if someone's name was 'jp.fornamess' it would match the string, obviously this is not what the OP wants.
April 3, 2023 at 5:46 pm
You might avoid some false matches by using:
like '%' + p.forenames + ' %'
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".
April 3, 2023 at 6:12 pm
Ah gotcha Jonathan AC Roberts missed seeing that bit of incongruity
ScottPletcher how is yours different than what Jonathan had already put forth -- they look identical am I missing something?
April 3, 2023 at 7:57 pm
I put a space after the embedded forename (because of the wording of the OP: "who put in their middle name or surname with forenames separated by a space".)
Otherwise, I think maybe you could get a false match if the first/last name were like 'Jack Jackson'?! Not 100% sure, just something I thought you should check for.
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".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply