Mdthbs

Interpretation of ROC AUC score

If I arrive in the UK, and then head to mainland Europe, does my Schengen visa 90 day limit start when I arrived in the UK, or mainland Europe?

Final exams: What is the most common protocol for scheduling?

How was Daenerys able to legitimise Gendry?

Creating second map without labels using QGIS?

Can a UK national work as a paid shop assistant in the USA?

Testing using real data of the customer

Navigating a quick return to previous employer

How to let other coworkers know that I don't share my coworker's political views?

A burglar's sunglasses, a lady's odyssey

Using too much dialogue?

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

How to keep consistency across the application architecture as a team grows?

Burned out due to current job, Can I take a week of vacation between jobs?

How to melt snow without fire or using body heat?

Which European Languages are not Indo-European?

What is the recommended procedure to land a taildragger in a crosswind?

Is this homebrew "Cactus Grenade" cantrip balanced?

3 prong range outlet

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

“For nothing” = “pour rien”?

What weight should be given to writers groups critiques?

Why is this integration method not valid?

Cardio work for Muay Thai fighters

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 8 hours ago

kevinnwhatkevinnwhat

41717

• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @MikeJones I added some code that I think will work to check and see if this is actually the problem.
  
  – Josh Darnell
  6 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 8 hours ago

kevinnwhatkevinnwhat

41717

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I further tried: 'AND CHARINDEX('-', NEW_ADDRESS) >0' but this does not seem to work either
  
  – Mike Jones
  6 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
  5 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
  5 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 8 hours ago

kevinnwhatkevinnwhat

41717

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I further tried: 'AND CHARINDEX('-', NEW_ADDRESS) >0' but this does not seem to work either
  
  – Mike Jones
  6 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
  5 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
  5 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 8 hours ago

kevinnwhatkevinnwhat

41717

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

select charindex('-','kevin')

share|improve this answer

answered 8 hours ago

kevinnwhatkevinnwhat

41717

answered 8 hours ago

kevinnwhatkevinnwhat

41717

answered 8 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 6 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
  6 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
  6 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @MikeJones I added some code that I think will work to check and see if this is actually the problem.
  
  – Josh Darnell
  6 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 6 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
  6 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
  6 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @MikeJones I added some code that I think will work to check and see if this is actually the problem.
  
  – Josh Darnell
  6 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 6 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 6 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