Etydhv

Has there been evidence of any other gods?

Is it a Munchausen Number?

What is the radius of the circle in this problem?

Find exact value of infinite series.

Names of the Six Tastes

Row vectors and column vectors (Mathematica vs Matlab)

Do Rabbis admit emotional involvement in their rulings?

How do carbureted and fuel injected engines compare in high altitude?

JSON DeSerialization Help?

Are on’yomi words loanwords?

What's an appropriate age to involve kids in life changing decisions?

Which spells are in some way related to shadows or the Shadowfell?

Lorentz invariance of Maxwell's equations in matter

"Estrontium" on poster

Two (probably) equal real numbers which are not proved to be equal?

Double underlining a result in a system of equations with calculation steps on the right side

What are these round pads on the bottom of a PCB?

Renting a house to a graduate student in my department

Is there a need for better software for writers?

Probability of taking balls without replacement from a bag question

Integral with DiracDelta. Can Mathematica be made to solve this?

Has everyone forgotten about wildfire?

Was Mohammed the most popular first name for boys born in Berlin in 2018?

A Latin text with dependency tree

Using SUBSTRING and RTRIM Together

Substring a result and renaming itTRIM() Function With Linked ServerConsolidating a row of data, based on previous rowsWhere should I put nvarchar(max) dimensions in my data warehouse?Is the 8KB row limit a “hard limit”?Unmarshalling Hierarchical JSON in SQL Server 2016+Substring without the first n charactersSql Substring and Search StringSUBSTRING and IF statementproblem with substring in mysql

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

1

I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field. I need to parse out the City and State Abbrev into separate fields. Currently I just have this to parse out the State, but I have no idea how to handle the City

RIGHT(RTRIM(alt.Address),2)

sql-server-2016 substring string-manipulation trim

share|improve this question

edited 3 hours ago

Andriy M

16.5k63675

asked 4 hours ago

Mike JonesMike Jones

626

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is there a delimiter between City and State like a comma? Or a space between? And is it consistent?
  
  – Jacob H
  4 hours ago
  

  
  
  
  

add a comment | 

1

I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field. I need to parse out the City and State Abbrev into separate fields. Currently I just have this to parse out the State, but I have no idea how to handle the City

RIGHT(RTRIM(alt.Address),2)

sql-server-2016 substring string-manipulation trim

share|improve this question

edited 3 hours ago

Andriy M

16.5k63675

asked 4 hours ago

Mike JonesMike Jones

626

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is there a delimiter between City and State like a comma? Or a space between? And is it consistent?
  
  – Jacob H
  4 hours ago
  

  
  
  
  

add a comment | 

I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field. I need to parse out the City and State Abbrev into separate fields. Currently I just have this to parse out the State, but I have no idea how to handle the City

RIGHT(RTRIM(alt.Address),2)

sql-server-2016 substring string-manipulation trim

share|improve this question

edited 3 hours ago

Andriy M

16.5k63675

asked 4 hours ago

Mike JonesMike Jones

626

RIGHT(RTRIM(alt.Address),2)

share|improve this question

edited 3 hours ago

Andriy M

16.5k63675

asked 4 hours ago

Mike JonesMike Jones

626

share|improve this question

edited 3 hours ago

Andriy M

16.5k63675

asked 4 hours ago

Mike JonesMike Jones

626

edited 3 hours ago

Andriy M

16.5k63675

edited 3 hours ago

Andriy M

16.5k63675

asked 4 hours ago

Mike JonesMike Jones

626

asked 4 hours ago

Mike JonesMike Jones

626

1 Answer
1

active

oldest

votes

3

Try this

select right(rtrim(alt.Address),2),
 rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))

A slight variation on the second expression (city), which works the same but uses fewer ltrim/rtrim calls (which makes it more concise, albeit not necessarily clearer; it depends on how explicit you prefer the logic to be):

select right(rtrim(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))

You can play with both options in this live demo at dbfiddle.uk.

share|improve this answer

edited 4 hours ago

Andriy M

16.5k63675

answered 4 hours ago

kevinnwhatkevinnwhat

8716

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces.
  
  – Andriy M
  4 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces.
  
  – kevinnwhat
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left.
  
  – Andriy M
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  This actually still works with spaces in between city and state abbreviation, give it a shot.
  
  – kevinnwhat
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers
  
  – Andriy M
  4 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%2f237688%2fusing-substring-and-rtrim-together%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

Try this

select right(rtrim(alt.Address),2),
 rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))

A slight variation on the second expression (city), which works the same but uses fewer ltrim/rtrim calls (which makes it more concise, albeit not necessarily clearer; it depends on how explicit you prefer the logic to be):

select right(rtrim(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))

You can play with both options in this live demo at dbfiddle.uk.

share|improve this answer

edited 4 hours ago

Andriy M

16.5k63675

answered 4 hours ago

kevinnwhatkevinnwhat

8716

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces.
  
  – Andriy M
  4 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces.
  
  – kevinnwhat
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left.
  
  – Andriy M
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  This actually still works with spaces in between city and state abbreviation, give it a shot.
  
  – kevinnwhat
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers
  
  – Andriy M
  4 hours ago
  

  
  
  
  

 | 
show 2 more comments

3

Try this

select right(rtrim(alt.Address),2),
 rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))

A slight variation on the second expression (city), which works the same but uses fewer ltrim/rtrim calls (which makes it more concise, albeit not necessarily clearer; it depends on how explicit you prefer the logic to be):

select right(rtrim(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))

You can play with both options in this live demo at dbfiddle.uk.

share|improve this answer

edited 4 hours ago

Andriy M

16.5k63675

answered 4 hours ago

kevinnwhatkevinnwhat

8716

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces.
  
  – Andriy M
  4 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces.
  
  – kevinnwhat
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left.
  
  – Andriy M
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  This actually still works with spaces in between city and state abbreviation, give it a shot.
  
  – kevinnwhat
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers
  
  – Andriy M
  4 hours ago
  

  
  
  
  

 | 
show 2 more comments

Try this

select right(rtrim(alt.Address),2),
 rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))

A slight variation on the second expression (city), which works the same but uses fewer ltrim/rtrim calls (which makes it more concise, albeit not necessarily clearer; it depends on how explicit you prefer the logic to be):

select right(rtrim(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))

You can play with both options in this live demo at dbfiddle.uk.

share|improve this answer

edited 4 hours ago

Andriy M

16.5k63675

answered 4 hours ago

kevinnwhatkevinnwhat

8716

select right(rtrim(alt.Address),2),
 rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))

select right(rtrim(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))

share|improve this answer

edited 4 hours ago

Andriy M

16.5k63675

answered 4 hours ago

kevinnwhatkevinnwhat

8716

edited 4 hours ago

Andriy M

16.5k63675

edited 4 hours ago

Andriy M

16.5k63675

answered 4 hours ago

kevinnwhatkevinnwhat

8716

answered 4 hours ago

kevinnwhatkevinnwhat

8716