Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

remove / from data in SQL Server

Posted on 2013-12-01
4
489 Views
Last Modified: 2013-12-01
Hello Experts, I have a little problem (some may say a big problem .... but lets not go there); now that we have moved past the innuendo :D I have a field on a MS SQL 2012 table that contains data in to following format:

xx//xxxxxxx or xx/x/xxxxxxxx or A/a/xxxxxxx

the data type is NVARCHAR the values represent cadastral parcels.

my question is how do I remove ONLY / from the field in a view (it is causing some problems when rendering in another application).

Thankyou in advance
0
Comment
Question by:XGIS
  • 3
4 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39688798
In a view you need to use a string function to change the field's value. If you want to remove all / characters, then you need to use a REPLACE function. Assuming, you table is called Table1, and the column with the values is called Column1, then you can create a view, like this:
CREATE VIEW View1 AS
SELECT REPLACE(Column1, N'/', N'') AS Column1,
Column2,
Column3 -etc. List all other columns required for your application
FROM Table1
-- optionally you can JOIN other tables, add some filters using a WHERE clause, etc.

Open in new window

0
 
LVL 7

Author Comment

by:XGIS
ID: 39688800
Hello all, just after posting I tried some different things and found the replace function..... I know ....duh right but any way the more you know.

REPLACE(column, '/', '')

Thanks for your time.
0
 
LVL 7

Author Comment

by:XGIS
ID: 39688801
I've requested that this question be closed as follows:

Accepted answer: 0 points for XGIS's comment #a39688800

for the following reason:

Solved the solution myself
0
 
LVL 7

Author Closing Comment

by:XGIS
ID: 39688802
bit late but a solution none the less...
thanks.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question