Solved

remove / from data in SQL Server

Posted on 2013-12-01
4
469 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now