Mfcttrf

Why did Missandei say this?

Was the Highlands Ranch shooting the 115th mass shooting in the US in 2019

Thawing Glaciers return to hand interaction

Has everyone forgotten about wildfire?

What dice to use in a game that revolves around triangles?

What is a good way to allow only one non null field in an object

Can you turn a recording upside-down?

Locked my sa user out

Is there any evidence to support the claim that the United States was "suckered into WW1" by Zionists, made by Benjamin Freedman in his 1961 speech

How to avoid making self and former employee look bad when reporting on fixing former employee's work?

Compactness in normed vector spaces.

When do you stop "pushing" a book?

Why do the Avengers care about returning these items in Endgame?

Why are thrust reversers not used to slow down to taxi speeds?

What's the "magic similar to the Knock spell" referenced in the Dungeon of the Mad Mage adventure?

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

What can cause an unfrozen indoor copper drain pipe to crack?

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

Row vectors and column vectors (Mathematica vs Matlab)

Why is it wrong to *implement* myself a known, published, widely believed to be secure crypto algorithm?

if i accidentally leaked my schools ip address and someone d doses my school am i at fault

How to get MAX value using SOQL when there are more than 50,000 rows

Using wilcox.test() and t.test() in R yielding different p-values

How is Arya still alive?

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

Andriy M

16.5k63675

asked 4 hours ago

Mike JonesMike Jones

626

RIGHT(RTRIM(alt.Address),2)

share|improve this question

edited 4 hours ago

Andriy M

16.5k63675

asked 4 hours ago

Mike JonesMike Jones

626

share|improve this question

edited 4 hours ago

Andriy M

16.5k63675

asked 4 hours ago

Mike JonesMike Jones

626

edited 4 hours ago

Andriy M

16.5k63675

edited 4 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