?
Solved

remove / from data in SQL Server

Posted on 2013-12-01
4
Medium Priority
?
507 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 25

Accepted Solution

by:
chaau earned 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

807 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