Etydhv

Why does the hash of infinity have the digits of π?

How to melt snow without fire or using body heat?

Cardio work for Muay Thai fighters

What could a self-sustaining lunar colony slowly lose that would ultimately prove fatal?

Is there a simple example that empirical evidence is misleading?

Why does Bran want to find Drogon?

3 prong range outlet

Does an eye for an eye mean monetary compensation?

Why does splatting create a tuple on the rhs but a list on the lhs?

Navigating a quick return to previous employer

Dad jokes are fun

Count all vowels in string

Is this homebrew "Cactus Grenade" cantrip balanced?

Are runways booked by airlines to land their planes?

Co-author wants to put their current funding source in the acknowledgements section because they edited the paper

How to determine if a hyphen (-) exists inside a column

Is keeping the forking link on a true fork necessary (Github/GPL)?

How can I properly write this equation in Latex?

Why would a rational buyer offer to buy with no conditions precedent?

What would prevent living skin from being a good conductor for magic?

What did the 'turbo' button actually do?

Why was this character made Grand Maester?

Has Ursula Le Guin ever admitted to be influenced by Kibbutz for the Dispossessed?

Why did other houses not demand this?

How to determine if a hyphen (-) exists inside a column

Can I get similar Full Text Search functionality without using Full-Text Search?Indexing strategy for dynamic predicatePerforming search on a column by queryFormat column length in SSMS outputCheck for changes in a text columnHow do you select non-valid column names used by views / functions and SPs?Using SELECT within to_tsvector call in CREATE INDEXCan a Postgres LIKE statement return an exact match?Find closed sub directory with full text searchSearching for and parsing out specific text parts within the values of a column

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

3

In a CASE expression, I'm trying to search inside a text column to identify a hyphen (-):

CASE 
 WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') 
 AND al.NEW_ADDRESS CONTAINS '-' 
 THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

The hyphen can be located anywhere in the column, and so I just need to know if it exists, regardless of where it actually is in the column.

sql-server sql-server-2016 string-searching

share|improve this question

edited 8 hours ago

MDCCL

6,90331846

asked 8 hours ago

Mike JonesMike Jones

847

add a comment | 

3

In a CASE expression, I'm trying to search inside a text column to identify a hyphen (-):

CASE 
 WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') 
 AND al.NEW_ADDRESS CONTAINS '-' 
 THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

The hyphen can be located anywhere in the column, and so I just need to know if it exists, regardless of where it actually is in the column.

sql-server sql-server-2016 string-searching

share|improve this question

edited 8 hours ago

MDCCL

6,90331846

asked 8 hours ago

Mike JonesMike Jones

847

add a comment | 

In a CASE expression, I'm trying to search inside a text column to identify a hyphen (-):

CASE 
 WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') 
 AND al.NEW_ADDRESS CONTAINS '-' 
 THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

The hyphen can be located anywhere in the column, and so I just need to know if it exists, regardless of where it actually is in the column.

sql-server sql-server-2016 string-searching

share|improve this question

edited 8 hours ago

MDCCL

6,90331846

asked 8 hours ago

Mike JonesMike Jones

847

CASE 
 WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') 
 AND al.NEW_ADDRESS CONTAINS '-' 
 THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

share|improve this question

edited 8 hours ago

MDCCL

6,90331846

asked 8 hours ago

Mike JonesMike Jones

847

share|improve this question

edited 8 hours ago

MDCCL

6,90331846

asked 8 hours ago

Mike JonesMike Jones

847

edited 8 hours ago

MDCCL

6,90331846

edited 8 hours ago

MDCCL

6,90331846

asked 8 hours ago

Mike JonesMike Jones

847

asked 8 hours ago

Mike JonesMike Jones

847

2 Answers
2

active

oldest

votes

5

An alternative approach to the existing answer is to use the CHARINDEX() function which returns the position of the specified string if exists, otherwise 0.

select charindex('-','kevin-')

Will return 6, because the hyphen is located in the sixth position of the string, compared with

select charindex('-','kevin')

returns 0 because the '-' is not present in the string.

share|improve this answer

answered 7 hours ago

kevinnwhatkevinnwhat

41717

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I further tried: 'AND CHARINDEX('-', NEW_ADDRESS) >0' but this does not seem to work either
  
  – Mike Jones
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You could also include PATINDEX as a similar approach, though it allows for wildcards which may prove beneficial in certain circumstances.
  
  – John Eisbrener
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  PATINDEX worked perfect for this particular issue. It easily finds the position of the character and by using a >0 filter, it works like a charm!
  
  – Mike Jones
  4 hours ago
  

  
  
  
  

add a comment | 

7

You didn't mention why the code you provided doesn't work. CONTAINS is for use with SQL Server's full text search feature. If you're not using this, then you need to use a LIKE clause with wildcards:

CASE WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') AND al.NEW_ADDRESS LIKE '%-%' 
THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

Even if you are using full text search, the matching behavior with dashes can be unexpected and Microsoft recommends using LIKE instead.

---

You might double check that the character is really a hyphen, something like this should work:

SELECT 
 ASCII('-') as RealHypen,
 ASCII(SUBSTRING(NEW_ADDRESS, 1, 1))
FROM YourTable
WHERE ALT_ADDRESS = '2754 Churchill Circle';

share|improve this answer

edited 5 hours ago

answered 8 hours ago

Josh DarnellJosh Darnell

9,19232345

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm currently using the exact same code that @Josh provided but it doesn't work, because it doesn't return the correct data for several specific instances where I know that it "should" be. The exact text in ALT_ADDRESS is: "2754 Churchill Circle". The exact text in NEW_ADDRESS is: "O-89421". However, the results that are returned, does not include the NEW_ADDRESS (O-89421)
  
  – Mike Jones
  6 hours ago
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @MikeJones Yikes, that's tough! Have you double checked that the dash in NEW_ADDRESS is really the same dash as '-' (ASCII 45)? It could be an emdash or something else that looks dash-y.
  
  – Josh Darnell
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @JoshDarnell Holy Smokes, I never thought of that and now that I look at it, it does appear to be a little shorter than a normal hyphen. Is there a way to test and/or code around that possibility?
  
  – Mike Jones
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @MikeJones I added some code that I think will work to check and see if this is actually the problem.
  
  – Josh Darnell
  5 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think this is definitely on the right track, but that code assumes that the "character" is always in the first position, but I'd like for it to look through the entire string for a hyphen/ascii
  
  – Mike Jones
  5 hours ago
  

  
  
  
  

 | 
show 2 more comments

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f238714%2fhow-to-determine-if-a-hyphen-exists-inside-a-column%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

5

An alternative approach to the existing answer is to use the CHARINDEX() function which returns the position of the specified string if exists, otherwise 0.

select charindex('-','kevin-')

Will return 6, because the hyphen is located in the sixth position of the string, compared with

select charindex('-','kevin')

returns 0 because the '-' is not present in the string.

share|improve this answer

answered 7 hours ago

kevinnwhatkevinnwhat

41717

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I further tried: 'AND CHARINDEX('-', NEW_ADDRESS) >0' but this does not seem to work either
  
  – Mike Jones
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You could also include PATINDEX as a similar approach, though it allows for wildcards which may prove beneficial in certain circumstances.
  
  – John Eisbrener
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  PATINDEX worked perfect for this particular issue. It easily finds the position of the character and by using a >0 filter, it works like a charm!
  
  – Mike Jones
  4 hours ago
  

  
  
  
  

add a comment | 

5

An alternative approach to the existing answer is to use the CHARINDEX() function which returns the position of the specified string if exists, otherwise 0.

select charindex('-','kevin-')

Will return 6, because the hyphen is located in the sixth position of the string, compared with

select charindex('-','kevin')

returns 0 because the '-' is not present in the string.

share|improve this answer

answered 7 hours ago

kevinnwhatkevinnwhat

41717

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I further tried: 'AND CHARINDEX('-', NEW_ADDRESS) >0' but this does not seem to work either
  
  – Mike Jones
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You could also include PATINDEX as a similar approach, though it allows for wildcards which may prove beneficial in certain circumstances.
  
  – John Eisbrener
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  PATINDEX worked perfect for this particular issue. It easily finds the position of the character and by using a >0 filter, it works like a charm!
  
  – Mike Jones
  4 hours ago
  

  
  
  
  

add a comment | 

An alternative approach to the existing answer is to use the CHARINDEX() function which returns the position of the specified string if exists, otherwise 0.

select charindex('-','kevin-')

Will return 6, because the hyphen is located in the sixth position of the string, compared with

select charindex('-','kevin')

returns 0 because the '-' is not present in the string.

share|improve this answer

answered 7 hours ago

kevinnwhatkevinnwhat

41717

select charindex('-','kevin-')

select charindex('-','kevin')

share|improve this answer

answered 7 hours ago

kevinnwhatkevinnwhat

41717

answered 7 hours ago

kevinnwhatkevinnwhat

41717

answered 7 hours ago

kevinnwhatkevinnwhat

41717

7

You didn't mention why the code you provided doesn't work. CONTAINS is for use with SQL Server's full text search feature. If you're not using this, then you need to use a LIKE clause with wildcards:

CASE WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') AND al.NEW_ADDRESS LIKE '%-%' 
THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

Even if you are using full text search, the matching behavior with dashes can be unexpected and Microsoft recommends using LIKE instead.

---

You might double check that the character is really a hyphen, something like this should work:

SELECT 
 ASCII('-') as RealHypen,
 ASCII(SUBSTRING(NEW_ADDRESS, 1, 1))
FROM YourTable
WHERE ALT_ADDRESS = '2754 Churchill Circle';

share|improve this answer

edited 5 hours ago

answered 8 hours ago

Josh DarnellJosh Darnell

9,19232345

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm currently using the exact same code that @Josh provided but it doesn't work, because it doesn't return the correct data for several specific instances where I know that it "should" be. The exact text in ALT_ADDRESS is: "2754 Churchill Circle". The exact text in NEW_ADDRESS is: "O-89421". However, the results that are returned, does not include the NEW_ADDRESS (O-89421)
  
  – Mike Jones
  6 hours ago
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @MikeJones Yikes, that's tough! Have you double checked that the dash in NEW_ADDRESS is really the same dash as '-' (ASCII 45)? It could be an emdash or something else that looks dash-y.
  
  – Josh Darnell
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @JoshDarnell Holy Smokes, I never thought of that and now that I look at it, it does appear to be a little shorter than a normal hyphen. Is there a way to test and/or code around that possibility?
  
  – Mike Jones
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @MikeJones I added some code that I think will work to check and see if this is actually the problem.
  
  – Josh Darnell
  5 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think this is definitely on the right track, but that code assumes that the "character" is always in the first position, but I'd like for it to look through the entire string for a hyphen/ascii
  
  – Mike Jones
  5 hours ago
  

  
  
  
  

 | 
show 2 more comments

7

You didn't mention why the code you provided doesn't work. CONTAINS is for use with SQL Server's full text search feature. If you're not using this, then you need to use a LIKE clause with wildcards:

CASE WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') AND al.NEW_ADDRESS LIKE '%-%' 
THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

Even if you are using full text search, the matching behavior with dashes can be unexpected and Microsoft recommends using LIKE instead.

---

You might double check that the character is really a hyphen, something like this should work:

SELECT 
 ASCII('-') as RealHypen,
 ASCII(SUBSTRING(NEW_ADDRESS, 1, 1))
FROM YourTable
WHERE ALT_ADDRESS = '2754 Churchill Circle';

share|improve this answer

edited 5 hours ago

answered 8 hours ago

Josh DarnellJosh Darnell

9,19232345

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm currently using the exact same code that @Josh provided but it doesn't work, because it doesn't return the correct data for several specific instances where I know that it "should" be. The exact text in ALT_ADDRESS is: "2754 Churchill Circle". The exact text in NEW_ADDRESS is: "O-89421". However, the results that are returned, does not include the NEW_ADDRESS (O-89421)
  
  – Mike Jones
  6 hours ago
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @MikeJones Yikes, that's tough! Have you double checked that the dash in NEW_ADDRESS is really the same dash as '-' (ASCII 45)? It could be an emdash or something else that looks dash-y.
  
  – Josh Darnell
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @JoshDarnell Holy Smokes, I never thought of that and now that I look at it, it does appear to be a little shorter than a normal hyphen. Is there a way to test and/or code around that possibility?
  
  – Mike Jones
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @MikeJones I added some code that I think will work to check and see if this is actually the problem.
  
  – Josh Darnell
  5 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think this is definitely on the right track, but that code assumes that the "character" is always in the first position, but I'd like for it to look through the entire string for a hyphen/ascii
  
  – Mike Jones
  5 hours ago
  

  
  
  
  

 | 
show 2 more comments

You didn't mention why the code you provided doesn't work. CONTAINS is for use with SQL Server's full text search feature. If you're not using this, then you need to use a LIKE clause with wildcards:

CASE WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') AND al.NEW_ADDRESS LIKE '%-%' 
THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

Even if you are using full text search, the matching behavior with dashes can be unexpected and Microsoft recommends using LIKE instead.

---

You might double check that the character is really a hyphen, something like this should work:

SELECT 
 ASCII('-') as RealHypen,
 ASCII(SUBSTRING(NEW_ADDRESS, 1, 1))
FROM YourTable
WHERE ALT_ADDRESS = '2754 Churchill Circle';

share|improve this answer

edited 5 hours ago

answered 8 hours ago

Josh DarnellJosh Darnell

9,19232345

CASE WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') AND al.NEW_ADDRESS LIKE '%-%' 
THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

SELECT 
 ASCII('-') as RealHypen,
 ASCII(SUBSTRING(NEW_ADDRESS, 1, 1))
FROM YourTable
WHERE ALT_ADDRESS = '2754 Churchill Circle';

share|improve this answer

edited 5 hours ago

answered 8 hours ago

Josh DarnellJosh Darnell

9,19232345

answered 8 hours ago

Josh DarnellJosh Darnell

9,19232345

answered 8 hours ago

Josh DarnellJosh Darnell

9,19232345